Open Tabs
- README.md
- Project 1.ipynb
- Project1.3.ipynb
- jpm_dividends.csv
- README.md
- etf_analyzer.ipynb
- san_francisco_housing.ipynb
- financial_planning_tools.ipynb
- etf_analyzer.ipynb
Kernels
- devreinv.py
- exception_handling.ipynb
- financial_planning_tools.ipynb
- exception_handling.ipynb
- Project1.3.ipynb
- crypto_arbitrage.ipynb
- san_francisco_housing.ipynb
- Project 1.ipynb
- test.ipynb
- etf_analyzer.ipynb
Terminals
The JupyterLab extension providing Jupyter widgets.
A JupyterLab extension for rendering HoloViz content.
A JupyterLab preview extension for Voilà
A JupyterLab extension for version control using git
JupyterLab - Translation services
Jupyter server extension to supervise and proxy web services
JupyterLab - Application Utilities
JupyterLab - Translation services
JupyterLab - Table of Contents widget extension
JupyterLab - Debugger Extension
An extension for manipulating tags in cell metadata
An extension for manipulating tags in cell metadata
JupyterLab - Debugger Extension
JupyterLab extension for running LaTeX
GeoJSON renderer for JupyterLab
Pull Requests for JupyterLab
KaTeX math renderer for JupyterLab
Fasta renderer for JupyterLab
A JupyterLab extension.
A Jupyter widget for interfacing with MIDI controllers.
Language Server Protocol integration for JupyterLab
A side panel providing information and controls to run Apache Beam notebooks interactively.
a local versioning logging tool for jupyterlab notebooks
A JupyterLab extension for embedding drawio / mxgraph.
bqplot
TileDB notebook extension to prompt user for notebook options
Jump to definition of a variable or function in JupyterLab
Integrate 'react-toastify' nicely in JupyterLab.
A Jupyterlab extension to make, download and extract archive files.
EXPERIMENTAL: Save, reuse, and share code snippets using JupyterLab Code Snippets
A vCDAT extension for JupyterLab.
A JupyterLab extension for Dask.
Darcula theme for JupyterLab. Modelled after the classic Intellij theme.
JupyterLab 2/3 Solarized Dark extension.- A JupyterLab extension for embedding interactive drawio / mxgraph diagrams.
Allows toggling of code cells to execute automatically after opening a notebook.
A JupyterLab extension for annotating machine learning training sets using Prodigy
igv-jupyterlab is an extension for Jupyter Lab and traditional Jupyter Notebooks which wraps igv.js.- Jupyter Templates for IPyDrawio
nglview-js-widgets
Cognos Dashboard Embedded for JupyterLab
A JupyterLab extension.
Adds a spreadsheet viewer to JupyterLab
A JupyterLab extension for performing Vega transforms lazily using Ibis.- A JupyterLab theme extension which provides the Purple Please theme, a beautiful theme that's easy on the eyes.
- Measure the performance of Juxl-Applications.
- Juxl Vocabulary.
Save Jupyter Notebooks as Scripts or Markdown files that work well with version control & external text editors
Create Python Files from JupyterLab
AAS WorldWide Telescope in JupyterLab- Basic Logging capabilities for Juxl.
A JupyterLab extension.
JupyterLab extension to show a flash effect when a cell is executed
Insert text via shortcut keys in Jupyter Lab.
Code cell vim bindings- Interactive Jupyter genome browser
A JupyterLab theme extension inspired by Tailwind CSS.
Control JupyterLab from Python notebooks
Jupyter widgetds library for webgui js visualization library'
Adds two buttons to specific cells to show the features suggestions in jupyterlab
A Jupyter Lab extension for inspecting messages to/from a kernel
A cell toolbar for JupyterLab.
A JupyterLab extension.
Jupyterlab extension to show notebook cell completion browser notifications
A JupyterLab extension.
OpenBayes JupyterLab Theme with auto color scheme support powered by Kladenets
Add the ability to save favorite folders to JupyterLab for quicker browsing
A JupyterLab extension.
A JupyterLab UI Tour.
A custom widget for returning mouse and keyboard events to Python
ESASky Python wrapper
Track recent files and folders.
A enhanced launcher for JupyterLab
Insert text via shortcut keys in Jupyter Lab.
A Jupyter Widget Library for accessing the server's URL
Accompanies the EPI2ME labs experience. Not for general use.
AAS WorldWide Telescope from Python
A JupyterLab theme extension.
A Jupyter widget library for reactive variables
Adds two buttons to specific cells to show the features suggestions in jupyterlab
Emacs keybindings inside the notebook cells of JupyterLab.
A JupyterLab extension to control the configurable-http-proxy of JupyterHub.
Adds a play button to cells in jupyterlab
Create rich adata annotations in jupyter notebooks.- A Custom Jupyter Widget Library
A JupyterLab splash extension.
A JupyterLab extension.- JupyterLab viewer for GitHub repositories
- Measure the performance of Juxl-Applications.
A Jupyter widget for the Game of Life- Provide arbitrary shortcuts for apps in JupyterLab
A JupyterLab extension for creating and editing Plotly charts
Variable inspector extension for JupyterLab- [Ju]pyterLab [x]Api [l]ogging Extension. Provides a logging interface for JupyterLab Extensions.
Additional markdown rendering support in JupyterLab.
A JupyterLab extension that populates notebooks with URL parameters
ElkJS widget for Jupyter- Standard logging utility for juxl.
JupyterLab extension for SoS workflow engine and polyglot notebook
Use HERE Maps API for JavaScript in your Jupyter Notebook.
Jupyter widgets base for Vue libraries
A Jupyter extension for rendering Bokeh content.- A JupyterLab extension.
- Add testing cells to code cells.
- Add checkboxes to notebook cells.
- experimental Jupyter widgets for HTML5
- experimental Jupyter widgets for YAML
- Experimental widgets for JupyterLab
- experimental Jupyter widgets for the Lumino DataGrid
- experimental Jupyter widgets for JSON-E
- experimental Jupyter widgets for JSON Schema Form
- experimental Jupyter widgets for Nunjucks templates
- experimental Jupyter widgets for JSON-LD
- experimental Jupyter widgets for working with data in revision control
A port of the City Lights theme for JupyterLab
WIPP integration with JupyterLab
Collapses code cells in jupyterlab
Make headings collapsible like the old Jupyter notebook extension and like e.g. mathematica notebooks.
Hale Theme for Jupyter Lab 3.x. Inspired by Night Owl.
React JSON Schema Form for JupyterLab- OpenBayes Introduction
- Extends JupyterLab to display the state of Rigetti Forest software and services
- QControl3 integration for jupyterlab
Code formatter for JupyterLab.
Jupyter widgets based on vuetify UI components
Fira Code Fonts for JupyterLab- Experimental Jupyter widgets for JSON and evented transformations
Dejavu Sans Mono Fonts for JupyterLab
Manage your conda environments from JupyterLab
Anonymous Pro Fonts for JupyterLab
Manage your conda environments from JupyterLab
Completion theme manager for JupyterLab-LSP
jupyter - leaflet bridge
A spell checker for JupyterLab.
JupyterLab extension for the Chameleon testbed
Matplotlib Jupyter Interactive Widget
EXPERIMENTAL: JupyterLab Tabular Data Editor for CSV files- JupyterLab extension for browsing S3-compatible object storage
VSCode theme for JupyterLab-LSP
Material theme for JupyterLab-LSP
VSCode Horizon Theme port for JupyterLab
A flat, 80's neon inspired theme for JupyterLab 3.x.
Fast Datagrid widget for the Jupyter Notebook and JupyterLab- Eigendata is a low-code tool for data analysis designed for people that want to get things done.
JupyterLab Extension to share the URL to a running Jupyter Server
A Custom Jupyter Widget Library
JupyterLab extension to show the js logs from the browser dev tools console
A Cytoscape Jupyter Widget
Parameterized files and folders for JupyterLab
Snippets Extension for JupyterLab
Integration with Odahu Platform
Kite integration for JupyterLab
Video Chat with peers inside JupyterLab- VPython extension for JupyterLab
JupyterLab extension to show an interactive dependency graph of the installed plugins- greenflow Jupyterlab extension
JupyterLab spreadsheet (csv/tsv) editor- Neptune Labs extension for Jupyter
Simple canvas for ipywidgets
JupyterLab offline-notebook extension.
A JupyterLab extension.
The Material Darker theme for JupyterLab- A jupyterlab theme inspired by material darker
A JupyterLab launcher extension to view the molecular orbitals.
Common Lisp restart functionality for the JupyterLab debugger
Jupyter widget for rendering deck.gl in a Jupyter notebook
Stixview library widget for Jupyter- A Custom Jupyter Widget Library
A JupyterLab extension for rendering cube files.- A jupyterlab theme inspired by material darker
BeakerX: Beaker EasyForms, Magics, Plots and Spark Extension for Jupyter Lab 2.x- A JupyterLab extention to validate nbgrader-based notebooks.
Sublime notebook cell binding for JupyterLab
A JupyterLab Glue DataBrew extension.
A Jupyter widget for annotating images with bounding boxes
What-If Tool jupyter widget- A JupyterLab extension.
Menu Item to shutdown and change container specs
Display cell timings in Jupyter Lab
JupyterLab extension for visual hyperparamter tuning.
Sublime notebook cell binding for JupyterLab
jupyter widget bindings for the motif library
Sophon theme for JupyterLab- jpextensionbytanglei test by tanglei
- Syntax highlighting for KatScript within JupyterLab.
Prompt when JupyterLab window is closed- tanglei test
- tanglei test
A JupyterLab extension for authentication.
Connect to Jacdac devices from JupyterLab- A JupyterLab extension.
Change the prefix attached to context menu copied links- A JupyterLab extension.
- A JupyterLab extension.
- jupyterabc test
- Minimal JupyterLab example
- jpyabc test
JupyterLab viewer for Thredds catalog and ESGF- test extension for WEAV
A JupyterLab extension for version control using git- Hello world for widgets!
weav test
A simple and dark theme for Jupyter Lab- jupyterabc test by tanglei
- test extension for WEAV
test weav application
A JupyterLab extension.
Jupyter Lab extension to monitor Apache Spark Jobs- Jupyter labextension to make query params accessible in lab
- JupyterLab extension to toggle the theme in the Top Bar area
Spreadsheet in the Jupyter notebook.
Jupyter notebook extension to selectively hide code, prompts and outputs with PDF and HTML exporting support.
Interactive widgets library exposing the browser's Canvas API- experimental Jupyter widgets for SVG
Insert Infoboxes into Jupyter Notebooks- A JupyterLab extension that allows measurement of the power and energy usage of notebook code
JupyterLab extension of executing the scripts
A annotation Jupyter Widget based on Annotorius.
Interactive Typography and Style for JupyterLab
A JupyterLab extension to display, filter and allow download of notebooks from the Precision Health Cloud Tool Registry.- A Jupyter Widget for Kodexa
JupyterLab Top Bar interface- BabylonJS widget
- The Mito Spreadsheet
- A Custom Jupyter Widget Library
- OpenseaDragon for JupyterLab (including IIIF support)
- JupyterLab viewer for GitLab repositories
JupyterLab extension - Elyra theme- Jupyter widget for Vipster
Show a random
A glowing 80s theme based on Synthwave '84 and JupyterLab's Neon Night theme by yeebc
filebrowser overwrite
Show a random
Build Weav AI
Show a random
A JupyterLab extension.
Show a random
Show a random
Show a random
Show a random
Show a random
Show a random- Automatically run and/or hide cells when opening a Jupyter notebook
3D visualization library
Show Spark Application UI in a JupyterLab panel
jupyterlab extension to lint python code in the notebook and text editor
DO NOT USE: see ipycallback instead. This is for testing purposes. Use this widget to allow client side (javascript) to trigger event on the server side (python)
JupyterLab extension - Reusable code snippets for your Notebook and Python Scripts
Sets the active widget name (e.g. jupyter notebook name) as the browser tab name.
A JupyterLab extension for showing Notebook diffs.
- voila_etf_analyzer_files9 minutes ago
- etf_analyzer.ipynbseconds ago
- etf.db7 days ago
- voila_etf_analyzer.html9 minutes ago
- Create a Web Application for an ETF Analyzer
- Analyze a Single Asset in the ETF
- Optimize Data Access with Advanced SQL Queries
- Analyze the ETF Portfolio
- Deploy the Notebook as a Web Application
- Review the following code which imports the required libraries, initiates your SQLite database, popluates the database with records from the
etf.dbseed file that was included in your Starter_Code folder, creates the database engine, and confirms that data tables that it now contains. - Analyze a single asset in the FinTech ETF
- Step 1: Write a SQL
SELECTstatement by using an f-string that reads all the PYPL data from the database. Using the SQLSELECTstatement, execute a query that reads the PYPL data from the database into a Pandas DataFrame. - Step 2: Use the
headandtailfunctions to review the first five and the last five rows of the DataFrame. Make a note of the beginning and end dates that are available from this dataset. You’ll use this information to complete your analysis. - Step 3: Using hvPlot, create an interactive visualization for the PYPL daily returns. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.
- Step 4: Using hvPlot, create an interactive visualization for the PYPL cumulative returns. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.
- Optimize the SQL Queries
- Step 1: Access the closing prices for PYPL that are greater than 200 by completing the following steps:
- Step 2: Find the top 10 daily returns for PYPL by completing the following steps:
- Analyze the Fintech ETF Portfolio
- Step 1: Write a SQL query to join each table in the portfolio into a single DataFrame. To do so, complete the following steps:
- Step 2: Create a DataFrame that averages the “daily_returns” columns for all four assets. Review the resulting DataFrame.
- Step 3: Use the average daily returns in the etf_portfolio_returns DataFrame to calculate the annualized returns for the portfolio. Display the annualized return value of the ETF portfolio.
- Step 4: Use the average daily returns in the
etf_portfolio_returnsDataFrame to calculate the cumulative returns of the ETF portfolio. - Step 5: Using hvPlot, create an interactive line plot that visualizes the cumulative return values of the ETF portfolio. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.
xxxxxxxxxxxxxxxxxxxx- README.md
- Project 1.ipynb
- Project1.3.ipynb
- jpm_dividends.csv
- README.md
- etf_analyzer.ipynb
- san_francisco_housing.ipynb
- financial_planning_tools.ipynb
xxxxxxxxxxOption 3 No Dividend Reinvestment
xxxxxxxxxx# Import the required libraries and dependenciesimport osimport numpyimport requestsimport jsonimport pandas as pdfrom dotenv import load_dotenvimport alpaca_trade_api as tradeapifrom MCForecastTools import MCSimulationimport sysimport pandas as pdfrom pathlib import Path%matplotlib inlinex
stock = (input("Enter the stock you own: "))shares = float(input("Enter the number of shares: "))quarterly_dividend = float(input("Enter the quarterly dividend amount: "))yearly_rate = float(input("Enter the yearly rate of return: "))total_quarterly_dividend = quarterly_dividend * sharesquarterly_rate = yearly_rate/4/100years = int(input("Enter the number of years you want to simulate: "))quarters = years * 4futureValue = total_quarterly_dividend * ((((1 + quarterly_rate)**(quarters))-1) * (1 + quarterly_rate))/quarterly_ratefutureValue = round(futureValue)print("The expected amount of divdends from {stock} you will receive is:",futureValue) Enter the stock you own: msft Enter the number of shares: 300 Enter the quarterly dividend amount: .62 Enter the yearly rate of return: 1 Enter the number of years you want to simulate: 20
The expected amount of divdends from {stock} you will receive is: 16491
# Load the environment variables from the .env file# by calling the load_dotenv functionload_dotenv()xxxxxxxxxxEvaluate the MFST Holdings by Using the Alpaca SDK¶
In this section, you’ll determine the current value of a member’s stock and bond holdings. You’ll make an API call to Alpaca via the Alpaca SDK to get the current closing price of Microsoft (ticker: MSFT).For the prototype, assume that the member holds 300 shares of MSFT, which represents their portfolio. To do all this, complete the following steps:
In the
Starter_Codefolder, create an environment file (.env) to store the values of your Alpaca API key and Alpaca secret key.Set the variables for the Alpaca API and secret keys. Using the Alpaca SDK, create the Alpaca
tradeapi.RESTobject. In this object, include the parameters for the Alpaca API key, the secret key, and the version number.Set the following parameters for the Alpaca API call:
tickers: Use the tickers for the member’s stock and bond holdings.timeframe: Use a time frame of one day.start_dateandend_date: Use the same date for these parameters, and format them with the date of the previous weekday (or2020-08-07). This is because you want the one closing price for the most-recent trading day.
Get the current closing prices for
MSFTby using the Alpacaget_barsetfunction. Format the response as a Pandas DataFrame by including thedfproperty at the end of theget_barsetfunction.Navigating the Alpaca response DataFrame, select the
MSFTclosing prices, and store them as variables.Calculate the value, in US dollars, of the current amount of shares in each of the stock and bond portions of the portfolio, and print the results.
xxxxxxxxxxReview the total number of shares held in (MSFT).¶
# Current amount of shares held in MSFT.msft_shares = 300xxxxxxxxxxStep 1: In the Starter_Code folder, create an environment file (.env) to store the values of your Alpaca API key and Alpaca secret key.¶
xxxxxxxxxxStep 2: Set the variables for the Alpaca API and secret keys. Using the Alpaca SDK, create the Alpaca tradeapi.REST object. In this object, include the parameters for the Alpaca API key, the secret key, and the version number.¶
# Set the variables for the Alpaca API and secret keysalpaca_api_key = os.getenv("ALPACA_API_KEY")alpaca_secret_key = os.getenv("ALPACA_SECRET_KEY")# Create the Alpaca tradeapi.REST objectalpaca = tradeapi.REST( alpaca_api_key, alpaca_secret_key, api_version="v2")xxxxxxxxxxStep 3: Set the following parameters for the Alpaca API call:¶
tickers: Use the tickers for the member’s stock and bond holdings.timeframe: Use a time frame of one day.start_dateandend_date: Use the same date for these parameters, and format them with the date of the previous weekday (or2020-08-07). This is because you want the one closing price for the most-recent trading day.
xxxxxxxxxx# Set the tickers for both the bond and stock portion of the portfoliotickers = ['MSFT']# 'T', 'MO', 'JPM', 'F' ()# Set timeframe to 1D timeframe = "1D"# Format current date as ISO format# Set both the start and end date at the date of your prior weekday # This will give you the closing price of the previous trading day# Alternatively you can use a start and end date of 2020-08-07start_date = pd.Timestamp("2021-10-29", tz="America/New_York").isoformat()end_date = pd.Timestamp("2021-10-29", tz="America/New_York").isoformat()xxxxxxxxxxStep 4: Get the current closing prices for MSFT by using the Alpaca get_barset function. Format the response as a Pandas DataFrame by including the df property at the end of the get_barset function.¶
# Use the Alpaca get_barset function to get current closing prices the portfolio# Be sure to set the `df` property after the function to format the response object as a DataFramemsft_portfolio = alpaca.get_barset( tickers, timeframe, start = start_date, end = end_date).df# Review the first 5 rows of the Alpaca DataFramemsft_portfolio.head()xxxxxxxxxxStep 5: Navigating the Alpaca response DataFrame, select the 'MSFT` closing prices, and store them as variables.¶
# Access the closing price for MSFT from the Alpaca DataFrame# Converting the value to a floating point numbermsft_close_price = float(msft_portfolio['MSFT']['close'])# Print the MSFT closing priceprint(f'The MSFT closing price is: ${msft_close_price:.2f}')# Access the total value of the Microsoft holding by multiplying the closing price by the number of shares already determines.msft_value=msft_close_price*msft_shares# Set start and end dates of 5 years back from your current date# Alternatively, you can use an end date of 2020-08-07 and work 5 years back from that date start_date = "2016-10-29" end_date = "2021-10-29"# Set number of rows to 1000 to retrieve the maximum amount of rowslimit_rows=1000# Use the Alpaca get_barset function to make the API call to get the 3 years worth of pricing data# The tickers and timeframe parameters should have been set in Part 1 of this activity # The start and end dates should be updated with the information set above# Remember to add the df property to the end of the call so the response is returned as a DataFramefive_yr_portfolio = alpaca.get_barset( tickers, timeframe, start = start_date, end = end_date, limit = limit_rows).df# Display both the first and last five rows of the DataFramedisplay(five_yr_portfolio.head())display(five_yr_portfolio.tail())xxxxxxxxxxStep 2: Run a Monte Carlo simulation of 500 samples and 10 years for portfolio, and then plot the results.¶
xxxxxxxxxxStep 3: Plot the probability distribution of the Monte Carlo simulation.¶
xxxxxxxxxxStep 4: Generate the summary statistics for the Monte Carlo simulation.¶
xxxxxxxxxxAnalyze the Retirement Portfolio Forecasts¶
Using the current value of the member's portfolio and the summary statistics that you generated from the Monte Carlo simulation, answer the following question in your Jupyter notebook:
- What are the lower and upper bounds for the expected value of the portfolio with a 95% confidence interval?
# Print the current balance of the stock and bond portion of the members portfolioprint(f'The current balance of the MSFT portfolio is: ${msft_value:.2f}')# Configure a Monte Carlo simulation to forecast 10 years cumulative returns# Run 500 samples.ten_year_simulation = MCSimulation( portfolio_data=five_yr_portfolio, #weights=[0.20, 0.80], num_simulation=1000, num_trading_days=252*10)# Review the simulation input dataten_year_simulation.portfolio_data.head()# Run the Monte Carlo simulation to forecast 10 years cumulative returnsten_year_simulation.calc_cumulative_return()# Visualize the 10-year Monte Carlo simulation by creating an# overlay line plotten_year_simulation.plot_simulation()# Visualize the probability distribution of the 10-year Monte Carlo simulation # by plotting a histogramten_year_simulation.plot_distribution()# Generate summary statistics from the 10-year Monte Carlo simulation results# Save the results as a variableten_tbl=ten_year_simulation.summarize_cumulative_return()# Review the 10-year Monte Carlo summary statisticsten_tbl# Use the lower and upper `95%` confidence intervals to calculate the range of the possible outcomes for the current stock/bond portfolioci_lower_ten_cumulative_return = round(ten_tbl[8] * msft_value,2)ci_upper_ten_cumulative_return = round(ten_tbl[9] * msft_value,2)# Print the result of your calculationsprint(f"There is a 95% chance that your current stock/bond portfolio" f" over the next ten years will end within in the range of" f" ${ci_lower_ten_cumulative_return:.2f} and ${ci_upper_ten_cumulative_return:.2f}.")# Print the current balance of the stock and bond portion of the members portfolioprint(f'The current balance of the MSFT portfolio is: ${msft_value:.2f}')# Configure a Monte Carlo simulation to forecast 20 years cumulative returns# Run 1000 samples.twenty_year_simulation = MCSimulation( portfolio_data=five_yr_portfolio, #weights=[0.20, 0.80], num_simulation=1000, num_trading_days=252*20,)# Review the simulation input datatwenty_year_simulation.portfolio_data.head()# Run the Monte Carlo simulation to forecast 10 years cumulative returnstwenty_year_simulation.calc_cumulative_return()# Visualize the 10-year Monte Carlo simulation by creating an# overlay line plottwenty_year_simulation.plot_simulation()# Visualize the probability distribution of the 10-year Monte Carlo simulation # by plotting a histogramtwenty_year_simulation.plot_distribution()# Generate summary statistics from the 20-year Monte Carlo simulation results# Save the results as a variabletwenty_tbl=twenty_year_simulation.summarize_cumulative_return()# Review the 10-year Monte Carlo summary statisticstwenty_tbl# Use the lower and upper `95%` confidence intervals to calculate the range of the possible outcomes for the current stock/bond portfolioci_lower_twenty_cumulative_return = round(twenty_tbl[8] * msft_value,2)ci_upper_twenty_cumulative_return = round(twenty_tbl[9] * msft_value,2)# Print the result of your calculationsprint(f"There is a 95% chance that your current stock/bond portfolio" f" over the next year will end within in the range of" f" ${ci_lower_twenty_cumulative_return} and ${ci_upper_twenty_cumulative_return}.")# Print the current balance of the MSFT portfolioprint(f'The current balance of the MSFT portfolio is: ${msft_value:.2f}')# Configure the Monte Carlo simulation to forecast 30 years cumulative returns# Run 1000 samples.thirty_year_simulation = MCSimulation( portfolio_data = five_yr_portfolio, num_simulation = 1000, num_trading_days = 252 * 30,)# Review the simulation input datathirty_year_simulation.portfolio_data.head()# Set number of simulationsnum_sims = 1000# Run the Monte Carlo simulation to forecast 30 years cumulative returnsthirty_year_simulation.calc_cumulative_return()### Visualize the 30-year Monte Carlo simulation by creating an# overlay line plotthirty_year_simulation.plot_simulation()# Visualize the probability distribution of the 30-year Monte Carlo simulation # by plotting a histogramthirty_year_simulation.plot_distribution()# Generate summary statistics from the 30-year Monte Carlo simulation results# Save the results as a variablethirty_tbl=thirty_year_simulation.summarize_cumulative_return()# Review the 30-year Monte Carlo summary statisticsthirty_tbl# Use the lower and upper `95%` confidence intervals to calculate the range of the possible outcomes for the current stock/bond portfolioci_lower_thirty_cumulative_return = round(thirty_tbl[8] * msft_value,2)ci_upper_thirty_cumulative_return = round(thirty_tbl[9] * msft_value,2)# Print the result of your calculationsprint(f"There is a 95% chance that your current stock/bond portfolio" f" over the next year will end within in the range of" f" ${ci_lower_thirty_cumulative_return:.2f} and ${ci_upper_thirty_cumulative_return:.2f}.")xxxxxxxxxxThe Power of Dividend Reinvestment with APIs, CSV and Montecarlo Simulations¶
Microsoft and Bitcoin will be used in this analysis. Gather historical data over 5 year period of time using one stock (MSFT) and one crypto currencies(BTC).
We will provide 3 different scenarios:
- Dividend reinvested in MSFT over 10, 20 and 30 Year period.
- Dividend reinvested in Bitcoin over 10, 20 and 30 Year period.
- Dividend not reinvested.
we will use the information from the Monte Carlo simulation to answer questions about the portfolio.
# Import the required libraries and dependenciesimport osfrom pathlib import Pathimport requestsimport jsonimport pandas as pdfrom dotenv import load_dotenvimport alpaca_trade_api as tradeapiimport hvplot.pandasfrom MCForecastTools import MCSimulation%matplotlib inlinex
# Load the environment variables from the .env file# by calling the load_dotenv functionload_dotenv()True
xxxxxxxxxxEvaluate the Stock Holdings by Using the Alpaca SDK¶
In this section, we will determine the current value of the chosen stock holdings. We will make an API call to Alpaca via the Alpaca SDK to get the current closing prices of Microsoft (ticker: MSFT). For the prototype, assume that we hold 300 shares of MSFT, which represents the stock portfolio.
Create an environment file (
.env) to store the values of your Alpaca API key and Alpaca secret key.Set the variables for the Alpaca API and secret keys. Using the Alpaca SDK, create the Alpaca
tradeapi.RESTobject. In this object, include the parameters for the Alpaca API key, the secret key, and the version number.Set the following parameters for the Alpaca API call:
tickers: Use the tickers for Microsof.timeframe: Use a time frame of one day.start_dateandend_date: Use the same date for these parameters, and format them with the date of the previous weekday. This is because you want the one closing price for the most-recent trading day.
Get the current closing prices for
MSFTby using the Alpacaget_barsetfunction. Format the response as a Pandas DataFrame by including thedfproperty at the end of theget_barsetfunction.Navigating the Alpaca response DataFrame, select the
MSFTclosing prices, and store as variables.Calculate the value, in US dollars, of the current amount of shares of the stock portfolio, and print the results.
xxxxxxxxxxReview the total number of shares held in our starting portfolio (SPY)
# Current amount of shares held in the stock (MSFT) portfolio.msft_shares = 300 xxxxxxxxxx1: Create an environment file (.env) to store the values of your Alpaca API key and Alpaca secret key.¶
xxxxxxxxxx2. Set the variables for the Alpaca API and secret keys. Using the Alpaca SDK, create the Alpaca tradeapi.REST object. In this object, include the parameters for the Alpaca API key, the secret key, and the version number.¶
x
# Set Alpaca API key and secretalpaca_api_key = os.getenv("ALPACA_API_KEY")alpaca_secret_key = os.getenv("ALPACA_SECRET_KEY")x
# Create the Alpaca API objectalpaca = tradeapi.REST( alpaca_api_key, alpaca_secret_key, api_version="v2")xxxxxxxxxx3: Set the following parameters for the Alpaca API call:¶
tickers: Use the tickers for the member’s stock and bond holdings.timeframe: Use a time frame of one day.start_dateandend_date: Use the same date for these parameters, and format them with the date of the previous weekday. This is because you want the one closing price for the most-recent trading day.
x
# Set the tickerstickers = ["MSFT"]# Set timeframe to one day ('1D') for the Alpaca APItimeframe = "1D"# Format current date as ISO formatstart_date = pd.Timestamp("2021-10-29", tz="America/New_York").isoformat()end_date = pd.Timestamp("2021-10-29", tz="America/New_York").isoformat()xxxxxxxxxx4: Get the current closing prices for MSFT by using the Alpaca get_barset function. Format the response as a Pandas DataFrame by including the df property at the end of the get_barset function.¶
# Use the Alpaca get_barset function to get current closing prices the portfoliocurrent_msft_price_df = alpaca.get_barset( tickers, timeframe, start = start_date, end = end_date).df# Review the first 5 rows of the Alpaca DataFramecurrent_msft_price_df.head()| BTC | |||||
|---|---|---|---|---|---|
| open | high | low | close | volume | |
| time | |||||
xxxxxxxxxx5: Navigating the Alpaca response DataFrame, select the MSFT closing prices, and store them as variables.¶
# Access the closing price for MSFT from the Alpaca DataFrame# Converting the value to a floating point numbermsft_close_price = float(current_msft_price_df["MSFT"]["close"])# Print the MSFT closing priceprint(msft_close_price)331.64
xxxxxxxxxx6: Calculate the value, in US dollars, of the current amount of shares of the stock portfolio, and print the results.¶
# Calculate the current value of the portfoliomsft_value = round(msft_shares * msft_close_price, 3)# Print the current value of the bond portfolioprint(f"The portofolio has a value of: {msft_value}")The portofolio has a value of: 99492.0
xxxxxxxxxxUse the read_csv function and the Path module to read the msft_dividends.csv file into a Pandas DataFrame. Review the first five rows of the DataFrame by using the head function.¶
x
# Import the data by reading in the CSV file# Review the first 5 rows of the DataFramemsft_div = pd.read_csv(Path("Resources/msft_dividends.csv"))msft_div.head()| Announced | Period | Amount | Yield | Ex-Dividend | Record Date | Payable Date | |
|---|---|---|---|---|---|---|---|
| 0 | 9/14/2021 | quarterly | $0.62 | 0.83% | 11/17/2021 | 11/18/2021 | 12/9/2021 |
| 1 | 6/16/2021 | quarterly | $0.56 | 0.87% | 8/18/2021 | 8/19/2021 | 9/9/2021 |
| 2 | 3/16/2021 | quarterly | $0.56 | 0.94% | 5/19/2021 | 5/20/2021 | 6/10/2021 |
| 3 | 12/2/2020 | quarterly | $0.56 | 1.04% | 2/17/2021 | 2/18/2021 | 3/11/2021 |
| 4 | 9/15/2020 | quarterly | $0.56 | 1.07% | 11/18/2020 | 11/19/2020 | 12/10/2020 |
xxxxxxxxxxUse the str.replace function to remove the dollar signs ($) from the values in the Amount column.¶
# Use the str.replace function to remove the dollar sign, $msft_div.loc[:, "Amount"]= msft_div.loc[:, "Amount"].str.replace("$", "")/opt/anaconda3/envs/dev/lib/python3.7/site-packages/ipykernel_launcher.py:2: FutureWarning: The default value of regex will change from True to False in a future version. In addition, single character regular expressions will*not* be treated as literal strings when regex=True.
xxxxxxxxxxConvert the data type of the column to a float.¶
# Convert the data type to a floatmsft_div.loc[:, "Amount"]= msft_div.loc[:, "Amount"].astype("float")msft_div.dtypesAnnounced object Period object Amount float64 Yield object Ex-Dividend object Record Date object Payable Date object dtype: object
xxxxxxxxxxChoose columns of data on which to focus our analysis.¶
Select the data to analyze. Use loc to select the following columns of data:
- Amount
# Use loc to select `Timestamp (the index)` and `Close` from bitstamp DataFramemsft_div_slices = msft_div.loc[:, "Amount"]# Review the first five rows of the DataFramemsft_div_slices.head()0 0.62 1 0.56 2 0.56 3 0.56 4 0.56 Name: Amount, dtype: float64
xxxxxxxxxxCalculating the average dividends for MSFT¶
# Calculating the average quaterly dividend for MSFTmsft_div_slices_average = round(msft_div_slices.mean(),2)msft_div_slices_average0.46
# calculating the Yearly average dividend for MSFT shareaverage_year_div_msft = msft_div_slices_average * 4print(f"The average yearly dividend for Microsoft is: ${average_year_div_msft} per share.")The average yearly dividend for Microsoft is: $1.84 per share.
xxxxxxxxxxEvaluate the Bitcoin Value by Using the Requests Library¶
In this section, we will collect the current prices for the Bitcoin cryptocurrencies by using the Python Requests library. To do all this, complete the following steps:
Use the Requests library to get the current price (in US dollars) of Bitcoin (BTC) by using the API endpoints.
Navigate the JSON response object to access the current price of Bitcoin, and store it in a variable.
xxxxxxxxxxReview the endpoint URLs for the API calls to Free Crypto API in order to get the current pricing information for BTC.¶
# The Free Crypto API Call endpoint URLs for the held cryptocurrency assetsbtc_url = "https://api.alternative.me/v2/ticker/Bitcoin/?convert=USD"xxxxxxxxxx1. Use the Requests library to get the current price (in US dollars) of Bitcoin (BTC) by using the API endpoints.¶
# Using the Python requests library, make an API call to access the current price of BTCbtc_response = requests.get(btc_url).json()# Use the json.dumps function to review the response data from the API call# Use the indent and sort_keys parameters to make the response object readableprint(json.dumps(btc_response, indent=4, sort_keys=True)){
"data": {
"1": {
"circulating_supply": 18860950,
"id": 1,
"last_updated": 1635794052,
"max_supply": 21000000,
"name": "Bitcoin",
"quotes": {
"USD": {
"market_cap": 1148190624646,
"percent_change_1h": 0.273737200904498,
"percent_change_24h": 0.213312546616552,
"percent_change_7d": -0.318576816212922,
"percentage_change_1h": 0.273737200904498,
"percentage_change_24h": 0.213312546616552,
"percentage_change_7d": -0.318576816212922,
"price": 60978.0,
"volume_24h": 35977662583
}
},
"rank": 1,
"symbol": "BTC",
"total_supply": 18860950,
"website_slug": "bitcoin"
}
},
"metadata": {
"error": null,
"num_cryptocurrencies": 3105,
"timestamp": 1635794052
}
}
xxxxxxxxxx2.Navigate the JSON response object to access the current price of Bitcoin, and store it in a variable.¶
# Navigate the BTC response object to access the current price of BTCbtc_price = btc_response['data']['1']['quotes']['USD']['price']# Print the current price of BTCprint(f'The current price of BTC is ${btc_price:.2f}')The current price of BTC is $60978.00
xxxxxxxxxxOption 1: Reinvesting the dividens into Microsoft (MSFT)¶
xxxxxxxxxxCreate the Monte Carlo Simulation that will include reinstment of dividend on a yearly basis¶
In this section, we will use the MCForecastTools library to create a Monte Carlo simulation for the purpose of reinvesting the dividend into the stock.
Make an API call via the Alpaca SDK to get 5 years of historical closing prices for Microsft (MSFT).
Run a Monte Carlo simulations of 1000 samples for 10, 20 and 30 years for the portfolio, and then plot the results.
Plot the probability distribution of the Monte Carlo simulations. Plot the probability distribution of the Monte Carlo simulation.
Generate the summary statistics for the Monte Carlo simulation.
# impoprt New version of the Montecarlofrom MCForecastTools_2Mod import MCSimulationxxxxxxxxxxStep 1: Make an API call via the Alpaca SDK to get 5 years of historical closing prices for Microsoft.¶
# Set start and end dates of 3 years back from your current date# Alternatively, you can use an end date of 2020-08-07 and work 3 years back from that date start_date = pd.Timestamp("2016-10-29", tz="America/New_York").isoformat() end_date = pd.Timestamp("2021-10-29", tz="America/New_York").isoformat()# Set number of rows to 1000 to retrieve the maximum amount of rowslimit_rows = 1000# Use the Alpaca get_barset function to make the API call to get the 3 years worth of pricing data# The tickers and timeframe parameters should have been set early of this activity # The start and end dates should be updated with the information set above# Adding the df property to the end of the call so the response is returned as a DataFrameprices_df = alpaca.get_barset(tickers, timeframe, start= start_date, end= end_date, limit= limit_rows).df# Display both the first and last five rows of the DataFramedisplay(prices_df.head())display(prices_df.tail())| MSFT | |||||
|---|---|---|---|---|---|
| open | high | low | close | volume | |
| time | |||||
| 2017-11-09 00:00:00-05:00 | 84.11 | 84.270 | 82.90 | 84.09 | 16833031 |
| 2017-11-10 00:00:00-05:00 | 83.79 | 84.095 | 83.23 | 83.86 | 13902816 |
| 2017-11-13 00:00:00-05:00 | 83.66 | 83.940 | 83.46 | 83.93 | 9097109 |
| 2017-11-14 00:00:00-05:00 | 83.50 | 84.100 | 82.98 | 84.05 | 15246771 |
| 2017-11-15 00:00:00-05:00 | 83.47 | 83.690 | 82.69 | 82.99 | 14184790 |
| MSFT | |||||
|---|---|---|---|---|---|
| open | high | low | close | volume | |
| time | |||||
| 2021-10-25 00:00:00-04:00 | 309.54 | 309.5400 | 306.4600 | 308.30 | 13455241 |
| 2021-10-26 00:00:00-04:00 | 310.81 | 312.3950 | 308.5967 | 309.71 | 20748654 |
| 2021-10-27 00:00:00-04:00 | 316.00 | 326.0973 | 316.0000 | 323.14 | 45222243 |
| 2021-10-28 00:00:00-04:00 | 324.32 | 324.8700 | 321.3601 | 324.32 | 21324602 |
| 2021-10-29 00:00:00-04:00 | 324.14 | 332.0000 | 323.9000 | 331.64 | 26504382 |
xxxxxxxxxxStep 2a: Run a Monte Carlo simulation of 1000 samples and 10 years for Microsoft, and then plot the results.¶
# Configure the Monte Carlo simulation to forecast 10 years cumulative returns# Run 1000 samples.MC_ten_years = MCSimulation(portfolio_data = prices_df, num_simulation= 1000, num_trading_days= 252*10)# Review the simulation input dataMC_ten_years.portfolio_data.head()# We need to update the Montecarlo first for this part!!! | MSFT | ||||||
|---|---|---|---|---|---|---|
| open | high | low | close | volume | daily_return | |
| time | ||||||
| 2017-11-09 00:00:00-05:00 | 84.11 | 84.270 | 82.90 | 84.09 | 16833031 | NaN |
| 2017-11-10 00:00:00-05:00 | 83.79 | 84.095 | 83.23 | 83.86 | 13902816 | -0.002735 |
| 2017-11-13 00:00:00-05:00 | 83.66 | 83.940 | 83.46 | 83.93 | 9097109 | 0.000835 |
| 2017-11-14 00:00:00-05:00 | 83.50 | 84.100 | 82.98 | 84.05 | 15246771 | 0.001430 |
| 2017-11-15 00:00:00-05:00 | 83.47 | 83.690 | 82.69 | 82.99 | 14184790 | -0.012612 |
# Run the Monte Carlo simulation to forecast 10 years cumulative returnsMC_ten_years.calc_cumulative_return()# Visualize the 10-year Monte Carlo simulation by creating an# overlay line plotMC_ten_years.plot_simulation()xxxxxxxxxxPlot the probability distribution of the Monte Carlo simulation.¶
# Visualize the probability distribution of the 10-year Monte Carlo simulation # by plotting a histogramMC_thirty_years.plot_distribution()xxxxxxxxxxGenerate the summary statistics for the Monte Carlo simulation.¶
# Generate summary statistics from the 10-year Monte Carlo simulation results# Save the results as a variableMC_ten_summary_statistics = round(MC_ten_years.summarize_cumulative_return(), 3)# Review the 10-year Monte Carlo summary statisticsprint(MC_ten_summary_statistics)xxxxxxxxxxAnalyze the Dividend Reinvestment Into Microsoft Portfolio Forecasts¶
Using the current value of the stock portfolio and the summary statistics that was generated from the Monte Carlo simulation, let's see what are the lower and upper bounds for the expected value of the portfolio with a 95% confidence interval?
# Print the current value of the stock.print(f"The current value of the stock portfolio is: ${msft_value}")# Use the lower and upper `95%` confidence intervals to calculate the range of the possible outcomes for the portfolioci_lower_ten_cumulative_return = MC_ten_summary_statistics[8] * msft_valueci_upper_ten_cumulative_return = MC_ten_summary_statistics[9] * msft_value# Print the result of your calculationsprint(f"There is a 95% chance that you current protfolio over the next 30 years will end within in range of \n" f"${ci_lower_ten_cumulative_return: .2f} and ${ci_upper_ten_cumulative_return: .2f}")xxxxxxxxxxStep 2b: Run a Monte Carlo simulation of 1000 samples and 20 years for Microsoft, and then plot the results.¶
# Configure the Monte Carlo simulation to forecast 20 years cumulative returns# Run 1000 samples.MC_twenty_years = MCSimulation(portfolio_data = prices_df, num_simulation= 1000, num_trading_days= 252*20)# Review the simulation input dataMC_twenty_years.portfolio_data.head()# We need to update the Montecarlo first for this part!!! # Run the Monte Carlo simulation to forecast 20 years cumulative returnsMC_twenty_years.calc_cumulative_return()# Visualize the 20-year Monte Carlo simulation by creating an# overlay line plotMC_twenty_years.plot_simulation()xxxxxxxxxxPlot the probability distribution of the Monte Carlo simulation.¶
# Visualize the probability distribution of the 20-year Monte Carlo simulation # by plotting a histogramMC_thirty_years.plot_distribution()xxxxxxxxxxGenerate the summary statistics for the Monte Carlo simulation.¶
# Generate summary statistics from the 20-year Monte Carlo simulation results# Save the results as a variableMC_twenty_summary_statistics = round(MC_twenty_years.summarize_cumulative_return(), 3)# Review the 20-year Monte Carlo summary statisticsprint(MC_twenty_summary_statistics)xxxxxxxxxxAnalyze the Dividend Reinvestment Into Microsoft Portfolio Forecasts¶
Using the current value of the stock portfolio and the summary statistics that was generated from the Monte Carlo simulation, let's see what are the lower and upper bounds for the expected value of the portfolio with a 95% confidence interval?
# Print the current value of the stock.print(f"The current value of the stock portfolio is: ${msft_value}")# Use the lower and upper `95%` confidence intervals to calculate the range of the possible outcomes for the portfolioci_lower_twenty_cumulative_return = MC_twenty_summary_statistics[8] * msft_valueci_upper_twenty_cumulative_return = MC_twenty_summary_statistics[9] * msft_value# Print the result of your calculationsprint(f"There is a 95% chance that you current protfolio over the next 30 years will end within in range of \n" f"${ci_lower_twenty_cumulative_return: .2f} and ${ci_upper_twenty_cumulative_return: .2f}")xxxxxxxxxxStep 2c: Run a Monte Carlo simulation of 1000 samples and 30 years for Microsoft, and then plot the results.¶
# Configure the Monte Carlo simulation to forecast 30 years cumulative returns# Run 1000 samples.MC_thirty_years = MCSimulation(portfolio_data = prices_df, num_simulation= 1000, num_trading_days= 252*30)# Review the simulation input dataMC_thirty_years.portfolio_data.head()# We need to update the Montecarlo first for this part!!! # Run the Monte Carlo simulation to forecast 30 years cumulative returnsMC_thirty_years.calc_cumulative_return()# Visualize the 30-year Monte Carlo simulation by creating an# overlay line plotMC_thirty_years.plot_simulation()xxxxxxxxxxPlot the probability distribution of the Monte Carlo simulation.¶
# Visualize the probability distribution of the 30-year Monte Carlo simulation # by plotting a histogramMC_thirty_years.plot_distribution()xxxxxxxxxxGenerate the summary statistics for the Monte Carlo simulation.¶
# Generate summary statistics from the 30-year Monte Carlo simulation results# Save the results as a variableMC_thirty_summary_statistics = round(MC_thirty_years.summarize_cumulative_return(), 3)# Review the 30-year Monte Carlo summary statisticsprint(MC_thirty_summary_statistics)xxxxxxxxxxAnalyze the Dividend Reinvestment Into Microsoft Portfolio Forecasts¶
Using the current value of the stock portfolio and the summary statistics that was generated from the Monte Carlo simulation, let's see what are the lower and upper bounds for the expected value of the portfolio with a 95% confidence interval?
# Print the current value of the stock.print(f"The current value of the stock portfolio is: ${msft_value}")# Use the lower and upper `95%` confidence intervals to calculate the range of the possible outcomes for the portfolioci_lower_thirty_cumulative_return = MC_thirty_summary_statistics[8] * msft_valueci_upper_thirty_cumulative_return = MC_thirty_summary_statistics[9] * msft_value# Print the result of your calculationsprint(f"There is a 95% chance that you current protfolio over the next 30 years will end within in range of \n" f"${ci_lower_thirty_cumulative_return: .2f} and ${ci_upper_thirty_cumulative_return: .2f}")xxxxxxxxxxOption 2: Reinvesting the dividens into Bitcoin Cryptocurrency (BTC)¶
xxxxxxxxxxCreate the Monte Carlo Simulation that will include reinstment of dividend on a yearly basis¶
In this section, we will use the MCForecastTools library to create a Monte Carlo simulation for the purpose of reinvesting the dividend into the chosen crypto.
Make an API call via the Alpaca SDK to get 5 years of historical closing prices for Bitcoin (BTC).
Run a Monte Carlo simulations of 1000 samples for 10, 20 and 30 years for the portfolio, and then plot the results.
Plot the probability distribution of the Monte Carlo simulations. Plot the probability distribution of the Monte Carlo simulation.
Generate the summary statistics for the Monte Carlo simulation.
xxxxxxxxxxOption 3: Keep the dividens as cash and see where Microsoft (MSFT) could be without reinvesting¶
xxxxxxxxxxCreate the Monte Carlo Simulation that will include reinstment of dividend on a yearly basis¶
In this section, we will use the MCForecastTools library to create a Monte Carlo simulation for Microsoft (MSFT)
Make an API call via the Alpaca SDK to get 5 years of historical closing prices for Microsoft (MSFT).
Run a Monte Carlo simulations of 1000 samples for 10, 20 and 30 years for the portfolio, and then plot the results.
Plot the probability distribution of the Monte Carlo simulations. Plot the probability distribution of the Monte Carlo simulation.
Generate the summary statistics for the Monte Carlo simulation.
Add the total accumulation of dividend.
class stock(): def __init__(self,name,wkn): self.name = name self.wkn = wkn def set_div_values(self,div_yield,div_growth,share_growth_pa,price): self.price = price self.div_yield = div_yield / 100 self.div_growth = div_growth / 100 self.share_growth_pa = share_growth_pa / 100 self.div = price * div_yield / 100 self.div_sum = 0 # calc compounded dividends p.a grown over time def calc_div(self,time, reinvest = True): dividends = dict() dividends[0] = [self.div] if reinvest is True: for t in range(1,time+1): temp = [] for i in range(t): temp.append(dividends[t-1][i]*(1+self.div_growth)) div_sum_reinvest = sum(dividends[t - 1]) temp.append(div_sum_reinvest*self.div_yield) dividends[t] = temp n = len(dividends.keys())-1 div_pa = sum(dividends[n]) else: div_pa = self.div for t in range(1,time+1): div_pa = div_pa * (1+self.div_growth) #print(f"{t}: Div: {div} ") return div_pa # calc compount interest def calc_endcap(self,time): return self.price * pow(1+self.share_growth_pa,time)coke = stock("Coca Cola","850663")coke.set_div_values(3.1,5,5,100)coke_dividends_re = coke.calc_div(20)coke_dividends = coke.calc_div(20,False)coke_endcap = coke.calc_endcap(20)print(f"dividends reinveste:\t{coke_dividends_re}\ndividends not reinveste:\t{coke_dividends}\nendcap: \t{coke_endcap}") xxxxxxxxxxCreate a Web Application for an ETF Analyzer¶
In this Challenge assignment, you’ll build a financial database and web application by using SQL, Python, and the Voilà library to analyze the performance of a hypothetical fintech ETF.
Instructions:
Use this notebook to complete your analysis of a fintech ETF that consists of four stocks: GOST, GS, PYPL, and SQ. Each stock has its own table in the etf.db database, which the Starter_Code folder also contains.
Analyze the daily returns of the ETF stocks both individually and as a whole. Then deploy the visualizations to a web application by using the Voilà library.
The detailed instructions are divided into the following parts:
Analyze a single asset in the ETF
Optimize data access with Advanced SQL queries
Analyze the ETF portfolio
Deploy the notebook as a web application
Analyze a Single Asset in the ETF¶
For this part of the assignment, you’ll use SQL queries with Python, Pandas, and hvPlot to analyze the performance of a single asset from the ETF.
Complete the following steps:
Write a SQL
SELECTstatement by using an f-string that reads all the PYPL data from the database. Using the SQLSELECTstatement, execute a query that reads the PYPL data from the database into a Pandas DataFrame.Use the
headandtailfunctions to review the first five and the last five rows of the DataFrame. Make a note of the beginning and end dates that are available from this dataset. You’ll use this information to complete your analysis.Using hvPlot, create an interactive visualization for the PYPL daily returns. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.
Using hvPlot, create an interactive visualization for the PYPL cumulative returns. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.
Optimize Data Access with Advanced SQL Queries¶
For this part of the assignment, you’ll continue to analyze a single asset (PYPL) from the ETF. You’ll use advanced SQL queries to optimize the efficiency of accessing data from the database.
Complete the following steps:
Access the closing prices for PYPL that are greater than 200 by completing the following steps:
Write a SQL
SELECTstatement to select the dates where the PYPL closing price was higher than 200.0.Using the SQL statement, read the data from the database into a Pandas DataFrame, and then review the resulting DataFrame.
Select the “time” and “close” columns for those dates where the closing price was higher than 200.0.
Find the top 10 daily returns for PYPL by completing the following steps:
Write a SQL statement to find the top 10 PYPL daily returns. Make sure to do the following:
Use
SELECTto select only the “time” and “daily_returns” columns.Use
ORDERto sort the results in descending order by the “daily_returns” column.Use
LIMITto limit the results to the top 10 daily return values.
Using the SQL statement, read the data from the database into a Pandas DataFrame, and then review the resulting DataFrame.
Analyze the ETF Portfolio¶
For this part of the assignment, you’ll build the entire ETF portfolio and then evaluate its performance. To do so, you’ll build the ETF portfolio by using SQL joins to combine all the data for each asset.
Complete the following steps:
Write a SQL query to join each table in the portfolio into a single DataFrame. To do so, complete the following steps:
Use a SQL inner join to join each table on the “time” column. Access the “time” column in the
GDOTtable via theGDOT.timesyntax. Access the “time” columns from the other tables via similar syntax.Using the SQL query, read the data from the database into a Pandas DataFrame. Review the resulting DataFrame.
Create a DataFrame that averages the “daily_returns” columns for all four assets. Review the resulting DataFrame.
Hint Assuming that this ETF contains equally weighted returns, you can average the returns for each asset to get the average returns of the portfolio. You can then use the average returns of the portfolio to calculate the annualized returns and the cumulative returns. For the calculation to get the average daily returns for the portfolio, use the following code:
etf_portfolio_returns = etf_portfolio['daily_returns'].mean(axis=1)You can use the average daily returns of the portfolio the same way that you used the daily returns of a single asset.
Use the average daily returns in the
etf_portfolio_returnsDataFrame to calculate the annualized returns for the portfolio. Display the annualized return value of the ETF portfolio.
Hint To calculate the annualized returns, multiply the mean of the
etf_portfolio_returnsvalues by 252.To convert the decimal values to percentages, multiply the results by 100.
Use the average daily returns in the
etf_portfolio_returnsDataFrame to calculate the cumulative returns of the ETF portfolio.Using hvPlot, create an interactive line plot that visualizes the cumulative return values of the ETF portfolio. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.
Deploy the Notebook as a Web Application¶
For this part of the assignment, complete the following steps:
Use the Voilà library to deploy your notebook as a web application. You can deploy the web application locally on your computer.
Take a screen recording or screenshots to show how the web application appears when using Voilà. Include the recording or screenshots in the
README.mdfile for your GitHub repository.
xxxxxxxxxxReview the following code which imports the required libraries, initiates your SQLite database, popluates the database with records from the etf.db seed file that was included in your Starter_Code folder, creates the database engine, and confirms that data tables that it now contains.¶
# Importing the required libraries and dependenciesimport numpy as npimport pandas as pdimport hvplot.pandasimport sqlalchemyfrom pathlib import Pathfrom datetime import datetime# Create a temporary SQLite database and populate the database with content from the etf.db seed filedatabase_connection_string = 'sqlite:///etf.db'# Create an engine to interact with the SQLite databaseengine = sqlalchemy.create_engine(database_connection_string)# Confirm that table names contained in the SQLite database.engine.table_names()C:\Users\jakob\miniforge3\envs\dev\lib\site-packages\ipykernel_launcher.py:17: SADeprecationWarning: The Engine.table_names() method is deprecated and will be removed in a future release. Please refer to Inspector.get_table_names(). (deprecated since: 1.4)
['GDOT', 'GS', 'PYPL', 'SQ']
xxxxxxxxxxAnalyze a single asset in the FinTech ETF¶
For this part of the assignment, you’ll use SQL queries with Python, Pandas, and hvPlot to analyze the performance of a single asset from the ETF.
Complete the following steps:
Write a SQL
SELECTstatement by using an f-string that reads all the PYPL data from the database. Using the SQLSELECTstatement, execute a query that reads the PYPL data from the database into a Pandas DataFrame.Use the
headandtailfunctions to review the first five and the last five rows of the DataFrame. Make a note of the beginning and end dates that are available from this dataset. You’ll use this information to complete your analysis.Using hvPlot, create an interactive visualization for the PYPL daily returns. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.
Using hvPlot, create an interactive visualization for the PYPL cumulative returns. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.
xxxxxxxxxxStep 1: Write a SQL SELECT statement by using an f-string that reads all the PYPL data from the database. Using the SQL SELECT statement, execute a query that reads the PYPL data from the database into a Pandas DataFrame.¶
# Write a SQL query to SELECT all of the data from the PYPL tablequery = """SELECT * FROM PYPL;"""# Use the query to read the PYPL data into a Pandas DataFramepypl_dataframe = pd.read_sql_query(query, con=engine)# Reflect only date on time column pypl_dataframe["time"] = pd.to_datetime(pypl_dataframe["time"], infer_datetime_format= True)# changing index column to timepypl_dataframe.set_index("time", drop= True, inplace= True)xxxxxxxxxxStep 2: Use the head and tail functions to review the first five and the last five rows of the DataFrame. Make a note of the beginning and end dates that are available from this dataset. You’ll use this information to complete your analysis.¶
# View the first 5 rows of the DataFrame.pypl_dataframe.head()| open | high | low | close | volume | daily_returns | |
|---|---|---|---|---|---|---|
| time | ||||||
| 2016-12-16 | 39.90 | 39.90 | 39.12 | 39.32 | 7298861 | -0.005564 |
| 2016-12-19 | 39.40 | 39.80 | 39.11 | 39.45 | 3436478 | 0.003306 |
| 2016-12-20 | 39.61 | 39.74 | 39.26 | 39.74 | 2940991 | 0.007351 |
| 2016-12-21 | 39.84 | 40.74 | 39.82 | 40.09 | 5826704 | 0.008807 |
| 2016-12-22 | 40.04 | 40.09 | 39.54 | 39.68 | 4338385 | -0.010227 |
# View the last 5 rows of the DataFrame.pypl_dataframe.tail()| open | high | low | close | volume | daily_returns | |
|---|---|---|---|---|---|---|
| time | ||||||
| 2020-11-30 | 212.51 | 215.83 | 207.0900 | 214.200 | 8992681 | 0.013629 |
| 2020-12-01 | 217.15 | 220.57 | 214.3401 | 216.520 | 9148174 | 0.010831 |
| 2020-12-02 | 215.60 | 215.75 | 210.5000 | 212.660 | 6414746 | -0.017827 |
| 2020-12-03 | 213.33 | 216.93 | 213.1100 | 214.680 | 6463339 | 0.009499 |
| 2020-12-04 | 214.88 | 217.28 | 213.0100 | 217.235 | 2118319 | 0.011901 |
xxxxxxxxxxStep 3: Using hvPlot, create an interactive visualization for the PYPL daily returns. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.¶
# Create an interactive visualization with hvplot to plot the daily returns for PYPL.pypl_dataframe.hvplot( x= 'time', y= ['daily_returns'], title=('PYPL Daily Returns'), height=400, width=800)xxxxxxxxxxStep 4: Using hvPlot, create an interactive visualization for the PYPL cumulative returns. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.¶
# Create an interactive visaulization with hvplot to plot the cumulative returns for PYPL.pypl_cumulative_returns = (1+ pypl_dataframe['daily_returns']).cumprod()pypl_cumulative_returns.hvplot( x='time', title=('PYPL Cumulative Returns'), height=400, width=800)xxxxxxxxxxOptimize the SQL Queries¶
For this part of the assignment, you’ll continue to analyze a single asset (PYPL) from the ETF. You’ll use advanced SQL queries to optimize the efficiency of accessing data from the database.
Complete the following steps:
Access the closing prices for PYPL that are greater than 200 by completing the following steps:
Access the closing prices for PYPL that are greater than 200 by completing the following steps:
Write a SQL
SELECTstatement to select the dates where the PYPL closing price was higher than 200.0.Select the “time” and “close” columns for those dates where the closing price was higher than 200.0.
Using the SQL statement, read the data from the database into a Pandas DataFrame, and then review the resulting DataFrame.
Find the top 10 daily returns for PYPL by completing the following steps:
Write a SQL statement to find the top 10 PYPL daily returns. Make sure to do the following:
Use
SELECTto select only the “time” and “daily_returns” columns.Use
ORDERto sort the results in descending order by the “daily_returns” column.Use
LIMITto limit the results to the top 10 daily return values.
Using the SQL statement, read the data from the database into a Pandas DataFrame, and then review the resulting DataFrame.
xxxxxxxxxxStep 1: Access the closing prices for PYPL that are greater than 200 by completing the following steps:¶
- Write a SQL `SELECT` statement to select the dates where the PYPL closing price was higher than 200.0.
- Select the “time” and “close” columns for those dates where the closing price was higher than 200.0.
- Using the SQL statement, read the data from the database into a Pandas DataFrame, and then review the resulting DataFrame.
# Write a SQL SELECT statement to select the time and close columns # where the PYPL closing price was higher than 200.0.query = """SELECT time, close FROM PYPLWHERE close > 200"""# Using the query, read the data from the database into a Pandas DataFramepypl_higher_than_200 = pd.read_sql_query(query, con=engine)# Review the resulting DataFramepypl_higher_than_200| time | close | |
|---|---|---|
| 0 | 2020-08-05 00:00:00.000000 | 202.920 |
| 1 | 2020-08-06 00:00:00.000000 | 204.090 |
| 2 | 2020-08-25 00:00:00.000000 | 201.710 |
| 3 | 2020-08-26 00:00:00.000000 | 203.530 |
| 4 | 2020-08-27 00:00:00.000000 | 204.340 |
| 5 | 2020-08-28 00:00:00.000000 | 204.480 |
| 6 | 2020-08-31 00:00:00.000000 | 203.950 |
| 7 | 2020-09-01 00:00:00.000000 | 208.920 |
| 8 | 2020-09-02 00:00:00.000000 | 210.820 |
| 9 | 2020-09-03 00:00:00.000000 | 205.070 |
| 10 | 2020-10-12 00:00:00.000000 | 201.500 |
| 11 | 2020-10-13 00:00:00.000000 | 207.730 |
| 12 | 2020-10-14 00:00:00.000000 | 203.660 |
| 13 | 2020-10-15 00:00:00.000000 | 203.170 |
| 14 | 2020-10-16 00:00:00.000000 | 204.540 |
| 15 | 2020-10-19 00:00:00.000000 | 200.050 |
| 16 | 2020-10-20 00:00:00.000000 | 201.960 |
| 17 | 2020-10-21 00:00:00.000000 | 213.060 |
| 18 | 2020-10-22 00:00:00.000000 | 203.880 |
| 19 | 2020-10-23 00:00:00.000000 | 203.040 |
| 20 | 2020-10-27 00:00:00.000000 | 200.440 |
| 21 | 2020-11-05 00:00:00.000000 | 204.590 |
| 22 | 2020-11-06 00:00:00.000000 | 202.720 |
| 23 | 2020-11-23 00:00:00.000000 | 200.820 |
| 24 | 2020-11-24 00:00:00.000000 | 206.000 |
| 25 | 2020-11-25 00:00:00.000000 | 214.380 |
| 26 | 2020-11-27 00:00:00.000000 | 211.320 |
| 27 | 2020-11-30 00:00:00.000000 | 214.200 |
| 28 | 2020-12-01 00:00:00.000000 | 216.520 |
| 29 | 2020-12-02 00:00:00.000000 | 212.660 |
| 30 | 2020-12-03 00:00:00.000000 | 214.680 |
| 31 | 2020-12-04 00:00:00.000000 | 217.235 |
xxxxxxxxxxStep 2: Find the top 10 daily returns for PYPL by completing the following steps:¶
- Write a SQL statement to find the top 10 PYPL daily returns. Make sure to do the following:
* Use `SELECT` to select only the “time” and “daily_returns” columns.
* Use `ORDER` to sort the results in descending order by the “daily_returns” column.
* Use `LIMIT` to limit the results to the top 10 daily return values.
- Using the SQL statement, read the data from the database into a Pandas DataFrame, and then review the resulting DataFrame.
# Write a SQL SELECT statement to select the time and daily_returns columns# Sort the results in descending order and return only the top 10 return valuesquery= """SELECT time, daily_returnsFROM PYPLORDER BY daily_returns DESCLIMIT 10"""# Using the query, read the data from the database into a Pandas DataFramepypl_top_10_returns = pd.read_sql_query(query, con=engine)# Review the resulting DataFramepypl_top_10_returns| time | daily_returns | |
|---|---|---|
| 0 | 2020-03-24 00:00:00.000000 | 0.140981 |
| 1 | 2020-05-07 00:00:00.000000 | 0.140318 |
| 2 | 2020-03-13 00:00:00.000000 | 0.138700 |
| 3 | 2020-04-06 00:00:00.000000 | 0.100877 |
| 4 | 2018-10-19 00:00:00.000000 | 0.093371 |
| 5 | 2019-10-24 00:00:00.000000 | 0.085912 |
| 6 | 2020-11-04 00:00:00.000000 | 0.080986 |
| 7 | 2020-03-10 00:00:00.000000 | 0.080863 |
| 8 | 2020-04-22 00:00:00.000000 | 0.075321 |
| 9 | 2018-12-26 00:00:00.000000 | 0.074656 |
xxxxxxxxxxAnalyze the Fintech ETF Portfolio¶
For this part of the assignment, you’ll build the entire ETF portfolio and then evaluate its performance. To do so, you’ll build the ETF portfolio by using SQL joins to combine all the data for each asset.
Complete the following steps:
Write a SQL query to join each table in the portfolio into a single DataFrame. To do so, complete the following steps:
Use a SQL inner join to join each table on the “time” column. Access the “time” column in the
GDOTtable via theGDOT.timesyntax. Access the “time” columns from the other tables via similar syntax.Using the SQL query, read the data from the database into a Pandas DataFrame. Review the resulting DataFrame.
Create a DataFrame that averages the “daily_returns” columns for all four assets. Review the resulting DataFrame.
Hint Assuming that this ETF contains equally weighted returns, you can average the returns for each asset to get the average returns of the portfolio. You can then use the average returns of the portfolio to calculate the annualized returns and the cumulative returns. For the calculation to get the average daily returns for the portfolio, use the following code:
etf_portfolio_returns = etf_portfolio['daily_returns'].mean(axis=1)You can use the average daily returns of the portfolio the same way that you used the daily returns of a single asset.
Use the average daily returns in the
etf_portfolio_returnsDataFrame to calculate the annualized returns for the portfolio. Display the annualized return value of the ETF portfolio.
Hint To calculate the annualized returns, multiply the mean of the
etf_portfolio_returnsvalues by 252.To convert the decimal values to percentages, multiply the results by 100.
Use the average daily returns in the
etf_portfolio_returnsDataFrame to calculate the cumulative returns of the ETF portfolio.Using hvPlot, create an interactive line plot that visualizes the cumulative return values of the ETF portfolio. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.
xxxxxxxxxxStep 1: Write a SQL query to join each table in the portfolio into a single DataFrame. To do so, complete the following steps:¶
- Use a SQL inner join to join each table on the “time” column. Access the “time” column in the `GDOT` table via the `GDOT.time` syntax. Access the “time” columns from the other tables via similar syntax.
- Using the SQL query, read the data from the database into a Pandas DataFrame. Review the resulting DataFrame.
# Write a SQL query to join each table in the portfolio into a single DataFrame # Use the time column from each table as the basis for the joinquery = """SELECT *FROM GDOTJOIN GS ON GS.time = GDOT.timeJOIN PYPL ON PYPL.time = GDOT.timeJOIN SQ ON SQ.time = GDOT.time"""# Using the query, read the data from the database into a Pandas DataFrameetf_portfolio = pd.read_sql_query(query, con=engine)# Review the resulting DataFramecloseetf_portfolio.head()| time | open | high | low | close | volume | daily_returns | time | open | high | ... | close | volume | daily_returns | time | open | high | low | close | volume | daily_returns | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2016-12-16 00:00:00.000000 | 24.41 | 24.73 | 23.94 | 23.980 | 483544 | -0.023218 | 2016-12-16 00:00:00.000000 | 242.80 | 243.19 | ... | 39.32 | 7298861 | -0.005564 | 2016-12-16 00:00:00.000000 | 14.29 | 14.47 | 14.2300 | 14.375 | 4516341 | 0.017339 |
| 1 | 2016-12-19 00:00:00.000000 | 24.00 | 24.01 | 23.55 | 23.790 | 288149 | -0.007923 | 2016-12-19 00:00:00.000000 | 238.34 | 239.74 | ... | 39.45 | 3436478 | 0.003306 | 2016-12-19 00:00:00.000000 | 14.34 | 14.60 | 14.3000 | 14.360 | 3944657 | -0.001043 |
| 2 | 2016-12-20 00:00:00.000000 | 23.75 | 23.94 | 23.58 | 23.820 | 220341 | 0.001261 | 2016-12-20 00:00:00.000000 | 240.52 | 243.65 | ... | 39.74 | 2940991 | 0.007351 | 2016-12-20 00:00:00.000000 | 14.73 | 14.82 | 14.4100 | 14.490 | 5207412 | 0.009053 |
| 3 | 2016-12-21 00:00:00.000000 | 23.90 | 23.97 | 23.69 | 23.860 | 249189 | 0.001679 | 2016-12-21 00:00:00.000000 | 242.24 | 242.40 | ... | 40.09 | 5826704 | 0.008807 | 2016-12-21 00:00:00.000000 | 14.45 | 14.54 | 14.2701 | 14.380 | 3901738 | -0.007591 |
| 4 | 2016-12-22 00:00:00.000000 | 23.90 | 24.01 | 23.70 | 24.005 | 383139 | 0.006077 | 2016-12-22 00:00:00.000000 | 241.23 | 242.86 | ... | 39.68 | 4338385 | -0.010227 | 2016-12-22 00:00:00.000000 | 14.33 | 14.34 | 13.9301 | 14.040 | 3874004 | -0.023644 |
5 rows × 28 columns
xxxxxxxxxxStep 2: Create a DataFrame that averages the “daily_returns” columns for all four assets. Review the resulting DataFrame.¶
# Create a DataFrame that displays the mean value of the “daily_returns” columns for all four assets.etf_portfolio_returns = etf_portfolio["daily_returns"].mean(axis=1)# Review the resulting DataFrameetf_portfolio_returns0 -0.007038
1 -0.001216
2 0.008567
3 -0.001004
4 -0.008243
...
994 -0.014635
995 -0.003990
996 -0.006288
997 0.011246
998 0.009108
Length: 999, dtype: float64xxxxxxxxxxStep 3: Use the average daily returns in the etf_portfolio_returns DataFrame to calculate the annualized returns for the portfolio. Display the annualized return value of the ETF portfolio.¶
# Use the average daily returns provided by the etf_portfolio_returns DataFrame # to calculate the annualized return for the portfolio. year_trading_days = 252annualized_etf_portfolio_returns = etf_portfolio_returns.mean() * year_trading_days * 100# Display the annualized return value of the ETF portfolio.print(f'The annualized return value of the ETF portfolio is {annualized_etf_portfolio_returns:.2f}%')The annualized return value of the ETF portfolio is 43.83%
xxxxxxxxxxStep 4: Use the average daily returns in the etf_portfolio_returns DataFrame to calculate the cumulative returns of the ETF portfolio.¶
# Use the average daily returns provided by the etf_portfolio_returns DataFrame to calculate the cumulative returnsetf_cumulative_returns = ((1+ etf_portfolio_returns).cumprod())# Display the final cumulative return valueetf_cumulative_returns.head()0 0.992962 1 0.991755 2 1.000251 3 0.999246 4 0.991010 dtype: float64
xxxxxxxxxx### Step 5: Using hvPlot, create an interactive line plot that visualizes the cumulative return values of the ETF portfolio. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.Step 5: Using hvPlot, create an interactive line plot that visualizes the cumulative return values of the ETF portfolio. Reflect the “time” column of the DataFrame on the x-axis. Make sure that you professionally style and format your visualization to enhance its readability.¶
# Import the required libraries and dependenciesimport pandas as pdimport hvplot.pandasfrom pathlib import Pathxxxxxxxxxx## Import the data Import the data¶
# Using the read_csv function and Path module, create a DataFrame # by importing the sfo_neighorhobods_census_data.csv file from the Resources foldersfo_data_df = pd.read_csv( Path("Resources/sfo_neighborhoods_census_data.csv"))# Review the first and last five rows of the DataFramedisplay(sfo_data_df.head())display(sfo_data_df.tail())| year | neighborhood | sale_price_sqr_foot | housing_units | gross_rent | |
|---|---|---|---|---|---|
| 0 | 2010 | Alamo Square | 291.182945 | 372560 | 1239 |
| 1 | 2010 | Anza Vista | 267.932583 | 372560 | 1239 |
| 2 | 2010 | Bayview | 170.098665 | 372560 | 1239 |
| 3 | 2010 | Buena Vista Park | 347.394919 | 372560 | 1239 |
| 4 | 2010 | Central Richmond | 319.027623 | 372560 | 1239 |
| year | neighborhood | sale_price_sqr_foot | housing_units | gross_rent | |
|---|---|---|---|---|---|
| 392 | 2016 | Telegraph Hill | 903.049771 | 384242 | 4390 |
| 393 | 2016 | Twin Peaks | 970.085470 | 384242 | 4390 |
| 394 | 2016 | Van Ness/ Civic Center | 552.602567 | 384242 | 4390 |
| 395 | 2016 | Visitacion Valley | 328.319007 | 384242 | 4390 |
| 396 | 2016 | Westwood Park | 631.195426 | 384242 | 4390 |
xxxxxxxxxx---xxxxxxxxxx## Calculate and Plot the Housing Units per YearCalculate and Plot the Housing Units per Year¶
For this part of the assignment, use numerical and visual aggregation to calculate the number of housing units per year, and then visualize the results as a bar chart. To do so, complete the following steps:
Use the
groupbyfunction to group the data by year. Aggregate the results by themeanof the groups.Use the
hvplotfunction to plot thehousing_units_by_yearDataFrame as a bar chart. Make the x-axis represent theyearand the y-axis represent thehousing_units.Style and format the line plot to ensure a professionally styled visualization.
Note that your resulting plot should appear similar to the following image:

Answer the following question:
- What’s the overall trend in housing units over the period that you’re analyzing?
xxxxxxxxxx### Step 1: Use the `groupby` function to group the data by year. Aggregate the results by the `mean` of the groups.Step 1: Use the groupby function to group the data by year. Aggregate the results by the mean of the groups.¶
# Create a numerical aggregation that groups the data by the year and then averages the results.housing_units_year=sfo_data_df.groupby('year').mean() # Review the DataFramehousing_units_year| sale_price_sqr_foot | housing_units | gross_rent | |
|---|---|---|---|
| year | |||
| 2010 | 369.344353 | 372560 | 1239 |
| 2011 | 341.903429 | 374507 | 1530 |
| 2012 | 399.389968 | 376454 | 2324 |
| 2013 | 483.600304 | 378401 | 2971 |
| 2014 | 556.277273 | 380348 | 3528 |
| 2015 | 632.540352 | 382295 | 3739 |
| 2016 | 697.643709 | 384242 | 4390 |
xxxxxxxxxx### Step 2: Use the `hvplot` function to plot the `housing_units_by_year` DataFrame as a bar chart. Make the x-axis represent the `year` and the y-axis represent the `housing_units`.# Create a visual aggregation explore the housing units by yearhousing_units_year.hvplot.bar( x='year', y='housing_units', xlabel="Year", ylabel="Housing_Units", title=('Housing Units in San Francisco from 2010 to 2016'), ylim=(365000, 385000)).opts( yformatter='%.0f' )xxxxxxxxxx### Step 5: Answer the following question:Step 5: Answer the following question:¶
xxxxxxxxxx**Question:** What is the overall trend in housing_units over the period being analyzed?Question: What is the overall trend in housing_units over the period being analyzed?
Answer: There is a gradual increase of housing units per year over the 7 year period being analyzed.
xxxxxxxxxx---xxxxxxxxxx## Calculate and Plot the Average Sale Prices per Square FootCalculate and Plot the Average Sale Prices per Square Foot¶
For this part of the assignment, use numerical and visual aggregation to calculate the average prices per square foot, and then visualize the results as a bar chart. To do so, complete the following steps:
Group the data by year, and then average the results. What’s the lowest gross rent that’s reported for the years that the DataFrame includes?
Create a new DataFrame named
prices_square_foot_by_yearby filtering out the “housing_units” column. The new DataFrame should include the averages per year for only the sale price per square foot and the gross rent.Use hvPlot to plot the
prices_square_foot_by_yearDataFrame as a line plot.Hint This single plot will include lines for both
sale_price_sqr_footandgross_rent.Style and format the line plot to ensure a professionally styled visualization.
Note that your resulting plot should appear similar to the following image:

Use both the
prices_square_foot_by_yearDataFrame and interactive plots to answer the following questions:Did any year experience a drop in the average sale price per square foot compared to the previous year?
If so, did the gross rent increase or decrease during that year?
xxxxxxxxxx### Step 1: Group the data by year, and then average the results.Step 1: Group the data by year, and then average the results.¶
# Create a numerical aggregation by grouping the data by year and averaging the resultsgroup_df = sfo_data_df.groupby('year').mean() # Review the resulting DataFramegroup_df| sale_price_sqr_foot | housing_units | gross_rent | |
|---|---|---|---|
| year | |||
| 2010 | 369.344353 | 372560 | 1239 |
| 2011 | 341.903429 | 374507 | 1530 |
| 2012 | 399.389968 | 376454 | 2324 |
| 2013 | 483.600304 | 378401 | 2971 |
| 2014 | 556.277273 | 380348 | 3528 |
| 2015 | 632.540352 | 382295 | 3739 |
| 2016 | 697.643709 | 384242 | 4390 |
xxxxxxxxxx**Question:** What is the lowest gross rent reported for the years included in the DataFrame?Question: What is the lowest gross rent reported for the years included in the DataFrame?
Answer: The lowest gross rent reported was $1,239 in 2010.
xxxxxxxxxx### Step 2: Create a new DataFrame named `prices_square_foot_by_year` by filtering out the “housing_units” column. The new DataFrame should include the averages per year for only the sale price per square foot and the gross rent.Step 2: Create a new DataFrame named prices_square_foot_by_year by filtering out the “housing_units” column. The new DataFrame should include the averages per year for only the sale price per square foot and the gross rent.¶
# Filter out the housing_units column, creating a new DataFrame # Keep only sale_price_sqr_foot and gross_rent averages per yearprices_square_foot_by_year = group_df[['sale_price_sqr_foot','gross_rent']] # Review the DataFrameprices_square_foot_by_year| sale_price_sqr_foot | gross_rent | |
|---|---|---|
| year | ||
| 2010 | 369.344353 | 1239 |
| 2011 | 341.903429 | 1530 |
| 2012 | 399.389968 | 2324 |
| 2013 | 483.600304 | 2971 |
| 2014 | 556.277273 | 3528 |
| 2015 | 632.540352 | 3739 |
| 2016 | 697.643709 | 4390 |
xxxxxxxxxx### Step 3: Use hvPlot to plot the `prices_square_foot_by_year` DataFrame as a line plot.# Plot prices_square_foot_by_year. # Inclued labels for the x- and y-axes, and a title.prices_square_foot_by_year.hvplot( x='year', y= ['gross_rent', 'sale_price_sqr_foot'], xlabel="Year", ylabel="Gross Rent / Sale Price Per Square Foot ", title=('Housing Units in San Francisco from 2010 to 2016'), figsize=(10,5)).opts( yformatter='%.0f')xxxxxxxxxx### Step 6: Use both the `prices_square_foot_by_year` DataFrame and interactive plots to answer the following questions:Step 6: Use both the prices_square_foot_by_year DataFrame and interactive plots to answer the following questions:¶
xxxxxxxxxx**Question:** Did any year experience a drop in the average sale price per square foot compared to the previous year?Question: Did any year experience a drop in the average sale price per square foot compared to the previous year?
Answer: There was a slight drop in sale price per square foot in 2011 verus 2010
xxxxxxxxxx**Question:** If so, did the gross rent increase or decrease during that year?Question: If so, did the gross rent increase or decrease during that year?
Answer: Gross rent increased in 2011.
xxxxxxxxxx---xxxxxxxxxx## Compare the Average Sale Prices by NeighborhoodCompare the Average Sale Prices by Neighborhood¶
For this part of the assignment, use interactive visualizations and widgets to explore the average sale price per square foot by neighborhood. To do so, complete the following steps:
Create a new DataFrame that groups the original DataFrame by year and neighborhood. Aggregate the results by the
meanof the groups.Filter out the “housing_units” column to create a DataFrame that includes only the
sale_price_sqr_footandgross_rentaverages per year.Create an interactive line plot with hvPlot that visualizes both
sale_price_sqr_footandgross_rent. Set the x-axis parameter to the year (x="year"). Use thegroupbyparameter to create an interactive widget forneighborhood.Style and format the line plot to ensure a professionally styled visualization.
Note that your resulting plot should appear similar to the following image:

Use the interactive visualization to answer the following question:
- For the Anza Vista neighborhood, is the average sale price per square foot for 2016 more or less than the price that’s listed for 2012?
xxxxxxxxxx### Step 1: Create a new DataFrame that groups the original DataFrame by year and neighborhood. Aggregate the results by the `mean` of the groups.Step 1: Create a new DataFrame that groups the original DataFrame by year and neighborhood. Aggregate the results by the mean of the groups.¶
# Group by year and neighborhood and then create a new dataframe of the mean valuesprices_by_year_by_neighborhood = sfo_data_df.groupby(['year', 'neighborhood']).mean()# Review the DataFrameprices_by_year_by_neighborhood| sale_price_sqr_foot | housing_units | gross_rent | ||
|---|---|---|---|---|
| year | neighborhood | |||
| 2010 | Alamo Square | 291.182945 | 372560 | 1239 |
| Anza Vista | 267.932583 | 372560 | 1239 | |
| Bayview | 170.098665 | 372560 | 1239 | |
| Buena Vista Park | 347.394919 | 372560 | 1239 | |
| Central Richmond | 319.027623 | 372560 | 1239 | |
| ... | ... | ... | ... | ... |
| 2016 | Telegraph Hill | 903.049771 | 384242 | 4390 |
| Twin Peaks | 970.085470 | 384242 | 4390 | |
| Van Ness/ Civic Center | 552.602567 | 384242 | 4390 | |
| Visitacion Valley | 328.319007 | 384242 | 4390 | |
| Westwood Park | 631.195426 | 384242 | 4390 |
397 rows × 3 columns
xxxxxxxxxx### Step 2: Filter out the “housing_units” column to create a DataFrame that includes only the `sale_price_sqr_foot` and `gross_rent` averages per year.Step 2: Filter out the “housing_units” column to create a DataFrame that includes only the sale_price_sqr_foot and gross_rent averages per year.¶
# Filter out the housing_unitsprices_by_year_by_neighborhood = prices_by_year_by_neighborhood.filter( ['sale_price_sqr_foot', 'gross_rent'])# Review the first and last five rows of the DataFramedisplay(prices_by_year_by_neighborhood.head())display(prices_by_year_by_neighborhood.tail())| sale_price_sqr_foot | gross_rent | ||
|---|---|---|---|
| year | neighborhood | ||
| 2010 | Alamo Square | 291.182945 | 1239 |
| Anza Vista | 267.932583 | 1239 | |
| Bayview | 170.098665 | 1239 | |
| Buena Vista Park | 347.394919 | 1239 | |
| Central Richmond | 319.027623 | 1239 |
| sale_price_sqr_foot | gross_rent | ||
|---|---|---|---|
| year | neighborhood | ||
| 2016 | Telegraph Hill | 903.049771 | 4390 |
| Twin Peaks | 970.085470 | 4390 | |
| Van Ness/ Civic Center | 552.602567 | 4390 | |
| Visitacion Valley | 328.319007 | 4390 | |
| Westwood Park | 631.195426 | 4390 |
xxxxxxxxxx### Step 3: Create an interactive line plot with hvPlot that visualizes both `sale_price_sqr_foot` and `gross_rent`. Set the x-axis parameter to the year (`x="year"`). Use the `groupby` parameter to create an interactive widget for `neighborhood`.Step 3: Create an interactive line plot with hvPlot that visualizes both sale_price_sqr_foot and gross_rent. Set the x-axis parameter to the year (x="year"). Use the groupby parameter to create an interactive widget for neighborhood.¶
xxxxxxxxxx### Step 4: Style and format the line plot to ensure a professionally styled visualization.Step 4: Style and format the line plot to ensure a professionally styled visualization.¶
# Use hvplot to create an interactive line plot of the average price per square foot# The plot should have a dropdown selector for the neighborhoodprices_by_year_by_neighborhood.hvplot( groupby='neighborhood', x='year', y= ['gross_rent','sale_price_sqr_foot'], xlabel="Year", ylabel="Sale Price Per Square Foot/Gross Rent", figsize=(15,5), title= 'Sale Price Per Square Foot and Average Gross Rent - 2010-2016 - By Neighborhood')xxxxxxxxxx### Step 6: Use the interactive visualization to answer the following question:Step 6: Use the interactive visualization to answer the following question:¶
xxxxxxxxxx**Question:** For the Anza Vista neighborhood, is the average sale price per square foot for 2016 more or less than the price that’s listed for 2012? Question: For the Anza Vista neighborhood, is the average sale price per square foot for 2016 more or less than the price that’s listed for 2012?
Answer: The average price per square foot for 2016 is less than the price that's listed in 2012.
xxxxxxxxxx---xxxxxxxxxx## Build an Interactive Neighborhood MapBuild an Interactive Neighborhood Map¶
For this part of the assignment, explore the geospatial relationships in the data by using interactive visualizations with hvPlot and GeoViews. To build your map, use the sfo_data_df DataFrame (created during the initial import), which includes the neighborhood location data with the average prices. To do all this, complete the following steps:
Read the
neighborhood_coordinates.csvfile from theResourcesfolder into the notebook, and create a DataFrame namedneighborhood_locations_df. Be sure to set theindex_colof the DataFrame as “Neighborhood”.Using the original
sfo_data_dfDataframe, create a DataFrame namedall_neighborhood_info_dfthat groups the data by neighborhood. Aggregate the results by themeanof the group.Review the two code cells that concatenate the
neighborhood_locations_dfDataFrame with theall_neighborhood_info_dfDataFrame. Note that the first cell uses the Pandas concat function to create a DataFrame namedall_neighborhoods_df. The second cell cleans the data and sets the “Neighborhood” column. Be sure to run these cells to create theall_neighborhoods_dfDataFrame, which you’ll need to create the geospatial visualization.Using hvPlot with GeoViews enabled, create a
pointsplot for theall_neighborhoods_dfDataFrame. Be sure to do the following:Set the
sizeparameter to “sale_price_sqr_foot”.Set the
colorparameter to “gross_rent”.Set the
size_maxparameter to “25”.Set the
zoomparameter to “11”.
Note that your resulting plot should appear similar to the following image:

Use the interactive map to answer the following question:
- Which neighborhood has the highest gross rent, and which has the highest sale price per square foot?
xxxxxxxxxx### Step 1: Read the `neighborhood_coordinates.csv` file from the `Resources` folder into the notebook, and create a DataFrame named `neighborhood_locations_df`. Be sure to set the `index_col` of the DataFrame as “Neighborhood”.Step 1: Read the neighborhood_coordinates.csv file from the Resources folder into the notebook, and create a DataFrame named neighborhood_locations_df. Be sure to set the index_col of the DataFrame as “Neighborhood”.¶
# Load neighborhoods coordinates dataneighborhood_locations_df = pd.read_csv( Path("Resources/neighborhoods_coordinates.csv"), index_col=("Neighborhood"))# Review the DataFrameneighborhood_locations_df| Lat | Lon | |
|---|---|---|
| Neighborhood | ||
| Alamo Square | 37.791012 | -122.402100 |
| Anza Vista | 37.779598 | -122.443451 |
| Bayview | 37.734670 | -122.401060 |
| Bayview Heights | 37.728740 | -122.410980 |
| Bernal Heights | 37.728630 | -122.443050 |
| ... | ... | ... |
| West Portal | 37.740260 | -122.463880 |
| Western Addition | 37.792980 | -122.435790 |
| Westwood Highlands | 37.734700 | -122.456854 |
| Westwood Park | 37.734150 | -122.457000 |
| Yerba Buena | 37.792980 | -122.396360 |
73 rows × 2 columns
xxxxxxxxxx### Step 2: Using the original `sfo_data_df` Dataframe, create a DataFrame named `all_neighborhood_info_df` that groups the data by neighborhood. Aggregate the results by the `mean` of the group.Step 2: Using the original sfo_data_df Dataframe, create a DataFrame named all_neighborhood_info_df that groups the data by neighborhood. Aggregate the results by the mean of the group.¶
# Calculate the mean values for each neighborhoodall_neighborhood_info_df = sfo_data_df.groupby(['neighborhood']).mean()# Review the resulting DataFrameall_neighborhood_info_df| year | sale_price_sqr_foot | housing_units | gross_rent | |
|---|---|---|---|---|
| neighborhood | ||||
| Alamo Square | 2013.000000 | 366.020712 | 378401.00 | 2817.285714 |
| Anza Vista | 2013.333333 | 373.382198 | 379050.00 | 3031.833333 |
| Bayview | 2012.000000 | 204.588623 | 376454.00 | 2318.400000 |
| Bayview Heights | 2015.000000 | 590.792839 | 382295.00 | 3739.000000 |
| Bernal Heights | 2013.500000 | 576.746488 | 379374.50 | 3080.333333 |
| ... | ... | ... | ... | ... |
| West Portal | 2012.250000 | 498.488485 | 376940.75 | 2515.500000 |
| Western Addition | 2012.500000 | 307.562201 | 377427.50 | 2555.166667 |
| Westwood Highlands | 2012.000000 | 533.703935 | 376454.00 | 2250.500000 |
| Westwood Park | 2015.000000 | 687.087575 | 382295.00 | 3959.000000 |
| Yerba Buena | 2012.500000 | 576.709848 | 377427.50 | 2555.166667 |
73 rows × 4 columns
xxxxxxxxxx### Step 3: Review the two code cells that concatenate the `neighborhood_locations_df` DataFrame with the `all_neighborhood_info_df` DataFrame. Step 3: Review the two code cells that concatenate the neighborhood_locations_df DataFrame with the all_neighborhood_info_df DataFrame.¶
Note that the first cell uses the Pandas concat function to create a DataFrame named all_neighborhoods_df.
The second cell cleans the data and sets the “Neighborhood” column.
Be sure to run these cells to create the all_neighborhoods_df DataFrame, which you’ll need to create the geospatial visualization.
# Using the Pandas `concat` function, join the # neighborhood_locations_df and the all_neighborhood_info_df DataFrame# The axis of the concatenation is "columns".# The concat function will automatially combine columns with# identical information, while keeping the additional columns.all_neighborhoods_df = pd.concat( [neighborhood_locations_df, all_neighborhood_info_df], axis="columns", sort=False)# Review the resulting DataFrameall_neighborhoods_df| Lat | Lon | year | sale_price_sqr_foot | housing_units | gross_rent | |
|---|---|---|---|---|---|---|
| Alamo Square | 37.791012 | -122.402100 | 2013.000000 | 366.020712 | 378401.0 | 2817.285714 |
| Anza Vista | 37.779598 | -122.443451 | 2013.333333 | 373.382198 | 379050.0 | 3031.833333 |
| Bayview | 37.734670 | -122.401060 | 2012.000000 | 204.588623 | 376454.0 | 2318.400000 |
| Bayview Heights | 37.728740 | -122.410980 | 2015.000000 | 590.792839 | 382295.0 | 3739.000000 |
| Bernal Heights | 37.728630 | -122.443050 | NaN | NaN | NaN | NaN |
| ... | ... | ... | ... | ... | ... | ... |
| Yerba Buena | 37.792980 | -122.396360 | 2012.500000 | 576.709848 | 377427.5 | 2555.166667 |
| Bernal Heights | NaN | NaN | 2013.500000 | 576.746488 | 379374.5 | 3080.333333 |
| Downtown | NaN | NaN | 2013.000000 | 391.434378 | 378401.0 | 2817.285714 |
| Ingleside | NaN | NaN | 2012.500000 | 367.895144 | 377427.5 | 2509.000000 |
| Outer Richmond | NaN | NaN | 2013.000000 | 473.900773 | 378401.0 | 2817.285714 |
77 rows × 6 columns
# Call the dropna function to remove any neighborhoods that do not have dataall_neighborhoods_df = all_neighborhoods_df.reset_index().dropna()# Rename the "index" column as "Neighborhood" for use in the Visualizationall_neighborhoods_df= all_neighborhoods_df.rename(columns={"index": "Neighborhood"})# Review the resulting DataFrameall_neighborhoods_df| Neighborhood | Lat | Lon | year | sale_price_sqr_foot | housing_units | gross_rent | |
|---|---|---|---|---|---|---|---|
| 0 | Alamo Square | 37.791012 | -122.402100 | 2013.000000 | 366.020712 | 378401.00 | 2817.285714 |
| 1 | Anza Vista | 37.779598 | -122.443451 | 2013.333333 | 373.382198 | 379050.00 | 3031.833333 |
| 2 | Bayview | 37.734670 | -122.401060 | 2012.000000 | 204.588623 | 376454.00 | 2318.400000 |
| 3 | Bayview Heights | 37.728740 | -122.410980 | 2015.000000 | 590.792839 | 382295.00 | 3739.000000 |
| 5 | Buena Vista Park | 37.768160 | -122.439330 | 2012.833333 | 452.680591 | 378076.50 | 2698.833333 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 68 | West Portal | 37.740260 | -122.463880 | 2012.250000 | 498.488485 | 376940.75 | 2515.500000 |
| 69 | Western Addition | 37.792980 | -122.435790 | 2012.500000 | 307.562201 | 377427.50 | 2555.166667 |
| 70 | Westwood Highlands | 37.734700 | -122.456854 | 2012.000000 | 533.703935 | 376454.00 | 2250.500000 |
| 71 | Westwood Park | 37.734150 | -122.457000 | 2015.000000 | 687.087575 | 382295.00 | 3959.000000 |
| 72 | Yerba Buena | 37.792980 | -122.396360 | 2012.500000 | 576.709848 | 377427.50 | 2555.166667 |
69 rows × 7 columns
xxxxxxxxxx### Step 4: Using hvPlot with GeoViews enabled, create a `points` plot for the `all_neighborhoods_df` DataFrame. Be sure to do the following:Step 4: Using hvPlot with GeoViews enabled, create a points plot for the all_neighborhoods_df DataFrame. Be sure to do the following:¶
- Set the
geoparameter to True. - Set the
sizeparameter to “sale_price_sqr_foot”. - Set the
colorparameter to “gross_rent”. - Set the
frame_widthparameter to 700. - Set the
frame_heightparameter to 500. - Include a descriptive title.
# Create a plot to analyze neighborhood infoall_neighborhoods_df.hvplot.points( 'Lon', 'Lat', geo=True, size = 'sale_price_sqr_foot', color='gross_rent', alpha=0.8, tiles='OSM', frame_width = 700, frame_height = 500, hover_cols='Neighborhood')xxxxxxxxxx### Step 5: Use the interactive map to answer the following question:Step 5: Use the interactive map to answer the following question:¶
xxxxxxxxxx**Question:** Which neighborhood has the highest gross rent, and which has the highest sale price per square foot?Question: Which neighborhood has the highest gross rent, and which has the highest sale price per square foot?
Answer: Westwood Park had the highest gross rent and Union Square District had the highest sale price per square foot.
xxxxxxxxxx## Compose Your Data StoryCompose Your Data Story¶
Based on the visualizations that you have created, compose a data story that synthesizes your analysis by answering the following questions:
xxxxxxxxxx**Question:** How does the trend in rental income growth compare to the trend in sales prices? Does this same trend hold true for all the neighborhoods across San Francisco?Question: How does the trend in rental income growth compare to the trend in sales prices? Does this same trend hold true for all the neighborhoods across San Francisco?
Answer: The rental income trend has more of an accelerated growth tragectory than the gradual growth that represents the sales prices per square foot. This remains consistent with minor variations throughout all of the neighborhoods across San Francisco.
Question: What insights can you share with your company about the potential one-click, buy-and-rent strategy that they're pursuing? Do neighborhoods exist that you would suggest for investment, and why?
Answer: The housing units total increased by 3% from 2010 to 2016. This shows that the supply of housing units remain constant while the demand increases in San Francisco. The sale price per square foot increased by 117.1% and gross rent increased by 354% during that same period from 2010 to 2016. This shows an opportunity in this marketplace to capitalize on the potential one-click, buy and rent strategy. If the goal is to generate income and hold the properties, there are significant opportunities to identify neighborhoods with low sale price per square foot with high gross rent. As reflected in hvPlot with GeoViews enabled, you will notice that there is smaller circle with dark blue color on the southeast section of the map. I would suggest Silver Terrace as an investment due to the low sale price per square foot and high gross rent comparative to other neighborhoods in San Francisco with a 170.29 sale per square foot and gross rent of 3528.00.
xxxxxxxxxx# Financial Planning with APIs and SimulationsFinancial Planning with APIs and Simulations¶
In this Challenge, we've created a financial tool illustrating the power of dividend reinvestment using a single Jupyter notebook: Part 1: A financial planner for emergencies. The members will be able to use this tool to visualize their current savings. The members can then determine if they have enough reserves for an emergency fund.
Part 2: A financial planner for retirement. This tool will forecast the performance of their retirement portfolio in 30 years. To do this, the tool will make an Alpaca API call via the Alpaca SDK to get historical price data for use in Monte Carlo simulations.
You’ll use the information from the Monte Carlo simulation to answer questions about the portfolio in your Jupyter notebook.
xxxxxxxxxx# Import the required libraries and dependenciesimport osimport requestsimport jsonimport pandas as pdfrom dotenv import load_dotenvimport alpaca_trade_api as tradeapifrom MCForecastTools import MCSimulation%matplotlib inlinexxxxxxxxxx# Load the environment variables from the .env file# by calling the load_dotenv functionload_dotenv()True
xxxxxxxxxx## Part 1: Create a Financial Planner for EmergenciesPart 1: Create a Financial Planner for Emergencies¶
xxxxxxxxxx### Evaluate the Cryptocurrency Wallet by Using the Requests LibraryEvaluate the Cryptocurrency Wallet by Using the Requests Library¶
In this section, you’ll determine the current value of a member’s cryptocurrency wallet. You’ll collect the current prices for the Bitcoin and Ethereum cryptocurrencies by using the Python Requests library. For the prototype, you’ll assume that the member holds the 1.2 Bitcoins (BTC) and 5.3 Ethereum coins (ETH). To do all this, complete the following steps:
Create a variable named
monthly_income, and set its value to12000.Use the Requests library to get the current price (in US dollars) of Bitcoin (BTC) and Ethereum (ETH) by using the API endpoints that the starter code supplies.
Navigate the JSON response object to access the current price of each coin, and store each in a variable.
Hint Note the specific identifier for each cryptocurrency in the API JSON response. The Bitcoin identifier is
1, and the Ethereum identifier is1027.Calculate the value, in US dollars, of the current amount of each cryptocurrency and of the entire cryptocurrency wallet.
xxxxxxxxxx# The current number of coins for each cryptocurrency asset held in the portfolio.btc_coins = 1.2eth_coins = 5.3xxxxxxxxxx#### Step 1: Create a variable named `monthly_income`, and set its value to `12000`.Step 1: Create a variable named monthly_income, and set its value to 12000.¶
xxxxxxxxxx# The monthly amount for the member's household incomemonthly_income = 12000xxxxxxxxxx#### Review the endpoint URLs for the API calls to Free Crypto API in order to get the current pricing information for both BTC and ETH.Review the endpoint URLs for the API calls to Free Crypto API in order to get the current pricing information for both BTC and ETH.¶
xxxxxxxxxx# The Free Crypto API Call endpoint URLs for the held cryptocurrency assetsbtc_url = "https://api.alternative.me/v2/ticker/Bitcoin/?convert=USD"eth_url = "https://api.alternative.me/v2/ticker/Ethereum/?convert=USD"xxxxxxxxxx#### Step 2. Use the Requests library to get the current price (in US dollars) of Bitcoin (BTC) and Ethereum (ETH) by using the API endpoints that the starter code supplied.Step 2. Use the Requests library to get the current price (in US dollars) of Bitcoin (BTC) and Ethereum (ETH) by using the API endpoints that the starter code supplied.¶
xxxxxxxxxx# Using the Python requests library, make an API call to access the current price of BTCbtc_response = requests.get(btc_url).json()# Use the json.dumps function to review the response data from the API call# Use the indent and sort_keys parameters to make the response object readableprint(json.dumps(btc_response, indent=4, sort_keys=True)){
"data": {
"1": {
"circulating_supply": 18856225,
"id": 1,
"last_updated": 1635371610,
"max_supply": 21000000,
"name": "Bitcoin",
"quotes": {
"USD": {
"market_cap": 1113069757924,
"percent_change_1h": 0.418862862272553,
"percent_change_24h": -4.0950855132471,
"percent_change_7d": -8.40586549522251,
"percentage_change_1h": 0.418862862272553,
"percentage_change_24h": -4.0950855132471,
"percentage_change_7d": -8.40586549522251,
"price": 59094.0,
"volume_24h": 45963798340
}
},
"rank": 1,
"symbol": "BTC",
"total_supply": 18856225,
"website_slug": "bitcoin"
}
},
"metadata": {
"error": null,
"num_cryptocurrencies": 3105,
"timestamp": 1635371610
}
}
xxxxxxxxxx# Using the Python requests library, make an API call to access the current price ETHeth_response = requests.get(eth_url).json()# Use the json.dumps function to review the response data from the API call# Use the indent and sort_keys parameters to make the response object readableprint(json.dumps(eth_response, indent=4, sort_keys=True)){
"data": {
"1027": {
"circulating_supply": 118102719,
"id": 1027,
"last_updated": 1635371594,
"max_supply": 0,
"name": "Ethereum",
"quotes": {
"USD": {
"market_cap": 473585808743,
"percent_change_1h": 0.719398483949307,
"percent_change_24h": -5.75624760377625,
"percent_change_7d": 3.1730415665463,
"percentage_change_1h": 0.719398483949307,
"percentage_change_24h": -5.75624760377625,
"percentage_change_7d": 3.1730415665463,
"price": 4007.85,
"volume_24h": 30002251517
}
},
"rank": 2,
"symbol": "ETH",
"total_supply": 118102719,
"website_slug": "ethereum"
}
},
"metadata": {
"error": null,
"num_cryptocurrencies": 3105,
"timestamp": 1635371594
}
}
xxxxxxxxxx#### Step 3: Navigate the JSON response object to access the current price of each coin, and store each in a variable.Step 3: Navigate the JSON response object to access the current price of each coin, and store each in a variable.¶
xxxxxxxxxx# Navigate the BTC response object to access the current price of BTCbtc_price = btc_response['data']['1']['quotes']['USD']['price']# Print the current price of BTCprint(f'The current price of BTC is ${btc_price:.2f}')The current price of BTC is $59094.00
xxxxxxxxxx# Navigate the BTC response object to access the current price of ETHeth_price = eth_response['data']['1027']['quotes']['USD']['price']# Print the current price of ETHprint(f'The current price of BTC is ${eth_price:.2f}')The current price of BTC is $4007.85
xxxxxxxxxx### Step 4: Calculate the value, in US dollars, of the current amount of each cryptocurrency and of the entire cryptocurrency wallet.Step 4: Calculate the value, in US dollars, of the current amount of each cryptocurrency and of the entire cryptocurrency wallet.¶
x
# Compute the current value of the BTC holding btc_value = btc_coins * btc_price# Print current value of your holding in BTCprint(f'The current value of the BTC holding is ${btc_value:.2f}')The current value of the BTC holding is $70912.80
xxxxxxxxxx# Compute the current value of the ETH holding eth_value = eth_coins * eth_price# Print current value of your holding in ETHprint(f'The current value of the ETH holding is ${eth_value:.2f}')The current value of the ETH holding is $21241.60
xxxxxxxxxx# Compute the total value of the cryptocurrency wallet# Add the value of the BTC holding to the value of the ETH holdingtotal_crypto_wallet = btc_value + eth_value# Print current cryptocurrency wallet balanceprint(f'The current cryptocurrency wallet balance is ${total_crypto_wallet:.2f}')The current cryptocurrency wallet balance is $92154.40
xxxxxxxxxx### Evaluate the Stock and Bond Holdings by Using the Alpaca SDKEvaluate the Stock and Bond Holdings by Using the Alpaca SDK¶
In this section, you’ll determine the current value of a member’s stock and bond holdings. You’ll make an API call to Alpaca via the Alpaca SDK to get the current closing prices of the SPDR S&P 500 ETF Trust (ticker: SPY) and of the iShares Core US Aggregate Bond ETF (ticker: AGG). For the prototype, assume that the member holds 110 shares of SPY, which represents the stock portion of their portfolio, and 200 shares of AGG, which represents the bond portion. To do all this, complete the following steps:
In the
Starter_Codefolder, create an environment file (.env) to store the values of your Alpaca API key and Alpaca secret key.Set the variables for the Alpaca API and secret keys. Using the Alpaca SDK, create the Alpaca
tradeapi.RESTobject. In this object, include the parameters for the Alpaca API key, the secret key, and the version number.Set the following parameters for the Alpaca API call:
tickers: Use the tickers for the member’s stock and bond holdings.timeframe: Use a time frame of one day.start_dateandend_date: Use the same date for these parameters, and format them with the date of the previous weekday (or2020-08-07). This is because you want the one closing price for the most-recent trading day.
Get the current closing prices for
SPYandAGGby using the Alpacaget_barsetfunction. Format the response as a Pandas DataFrame by including thedfproperty at the end of theget_barsetfunction.Navigating the Alpaca response DataFrame, select the
SPYandAGGclosing prices, and store them as variables.Calculate the value, in US dollars, of the current amount of shares in each of the stock and bond portions of the portfolio, and print the results.
xxxxxxxxxx#### Review the total number of shares held in both (SPY) and (AGG).Review the total number of shares held in both (SPY) and (AGG).¶
xxxxxxxxxx# Current amount of shares held in both the stock (SPY) and bond (AGG) portion of the portfolio.spy_shares = 110agg_shares = 200xxxxxxxxxx#### Step 1: In the `Starter_Code` folder, create an environment file (`.env`) to store the values of your Alpaca API key and Alpaca secret key.Step 1: In the Starter_Code folder, create an environment file (.env) to store the values of your Alpaca API key and Alpaca secret key.¶
xxxxxxxxxx#### Step 2: Set the variables for the Alpaca API and secret keys. Using the Alpaca SDK, create the Alpaca `tradeapi.REST` object. In this object, include the parameters for the Alpaca API key, the secret key, and the version number.Step 2: Set the variables for the Alpaca API and secret keys. Using the Alpaca SDK, create the Alpaca tradeapi.REST object. In this object, include the parameters for the Alpaca API key, the secret key, and the version number.¶
xxxxxxxxxx# Set the variables for the Alpaca API and secret keysalpaca_api_key = os.getenv("ALPACA_API_KEY")alpaca_secret_key = os.getenv("ALPACA_SECRET_KEY")# Create the Alpaca tradeapi.REST objectalpaca = tradeapi.REST( alpaca_api_key, alpaca_secret_key, api_version="v2")xxxxxxxxxx#### Step 3: Set the following parameters for the Alpaca API call:Step 3: Set the following parameters for the Alpaca API call:¶
tickers: Use the tickers for the member’s stock and bond holdings.timeframe: Use a time frame of one day.start_dateandend_date: Use the same date for these parameters, and format them with the date of the previous weekday (or2020-08-07). This is because you want the one closing price for the most-recent trading day.
xxxxxxxxxx# Set the tickers for both the bond and stock portion of the portfoliotickers = ['SPY', 'AGG']# Set timeframe to 1D timeframe = "1D"# Format current date as ISO format# Set both the start and end date at the date of your prior weekday # This will give you the closing price of the previous trading day# Alternatively you can use a start and end date of 2020-08-07start_date = pd.Timestamp("2021-10-21", tz="America/New_York").isoformat()end_date = pd.Timestamp("2021-10-21", tz="America/New_York").isoformat()xxxxxxxxxx#### Step 4: Get the current closing prices for `SPY` and `AGG` by using the Alpaca `get_barset` function. Format the response as a Pandas DataFrame by including the `df` property at the end of the `get_barset` function.Step 4: Get the current closing prices for SPY and AGG by using the Alpaca get_barset function. Format the response as a Pandas DataFrame by including the df property at the end of the get_barset function.¶
xxxxxxxxxx# Use the Alpaca get_barset function to get current closing prices the portfolio# Be sure to set the `df` property after the function to format the response object as a DataFramedf_portfolio = alpaca.get_barset( tickers, timeframe, start = start_date, end = end_date).df# Review the first 5 rows of the Alpaca DataFramedf_portfolio.head()| AGG | SPY | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| open | high | low | close | volume | open | high | low | close | volume | |
| time | ||||||||||
| 2021-10-21 00:00:00-04:00 | 114.01 | 114.04 | 113.8423 | 113.85 | 4741154 | 451.76 | 453.829 | 451.31 | 453.61 | 33945007 |
xxxxxxxxxx#### Step 5: Navigating the Alpaca response DataFrame, select the `SPY` and `AGG` closing prices, and store them as variables.Step 5: Navigating the Alpaca response DataFrame, select the SPY and AGG closing prices, and store them as variables.¶
xxxxxxxxxx# Access the closing price for AGG from the Alpaca DataFrame# Converting the value to a floating point numberagg_close_price = float(df_portfolio['AGG']['close'])# Print the AGG closing priceprint(f'The AGG closing price is: ${agg_close_price:.2f}')The AGG closing price is: $113.85
xxxxxxxxxx# Access the closing price for SPY from the Alpaca DataFrame# Converting the value to a floating point numberspy_close_price = float(df_portfolio['SPY']['close']) # Print the SPY closing priceprint(f'The SPY closing price is: ${spy_close_price:.2f}')The SPY closing price is: $453.61
xxxxxxxxxx#### Step 6: Calculate the value, in US dollars, of the current amount of shares in each of the stock and bond portions of the portfolio, and print the results.Step 6: Calculate the value, in US dollars, of the current amount of shares in each of the stock and bond portions of the portfolio, and print the results.¶
xxxxxxxxxx# Calculate the current value of t"he bond portion of the portfolioagg_value = agg_close_price * agg_shares# Print the current value of the bond portfolioprint(f'The current value of the AGG Bond portfolio is: ${agg_value:.2f}')The current value of the AGG Bond portfolio is: $22770.00
xxxxxxxxxx# Calculate the current value of the stock portion of the portfoliospy_value = spy_shares * spy_close_price # Print the current value of the stock portfolioprint(f'The current value of the stock portion of the portfolio is: ${spy_value:.2f}')The current value of the stock portion of the portfolio is: $49897.10
xxxxxxxxxx# Calculate the total value of the stock and bond portion of the portfoliototal_stocks_bonds = agg_value + spy_value # Print the current balance of the stock and bond portion of the portfolioprint(f'The current balance of the stock and bond portion of the portfolio is: ${total_stocks_bonds:.2f}')The current balance of the stock and bond portion of the portfolio is: $72667.10
xxxxxxxxxx# Calculate the total value of the member's entire savings portfolio# Add the value of the cryptocurrency walled to the value of the total stocks and bondstotal_portfolio = total_stocks_bonds + total_crypto_wallet # Print current cryptocurrency wallet balanceprint(f'The total value of the entire savings portfolio is: ${total_portfolio:.2f}')The total value of the entire savings portfolio is: $164821.51
xxxxxxxxxx### Evaluate the Emergency FundEvaluate the Emergency Fund¶
In this section, you’ll use the valuations for the cryptocurrency wallet and for the stock and bond portions of the portfolio to determine if the credit union member has enough savings to build an emergency fund into their financial plan. To do this, complete the following steps:
Create a Python list named
savings_datathat has two elements. The first element contains the total value of the cryptocurrency wallet. The second element contains the total value of the stock and bond portions of the portfolio.Use the
savings_datalist to create a Pandas DataFrame namedsavings_df, and then display this DataFrame. The function to create the DataFrame should take the following three parameters:savings_data: Use the list that you just created.columns: Set this parameter equal to a Python list with a single value calledamount.index: Set this parameter equal to a Python list with the values ofcryptoandstock/bond.
Use the
savings_dfDataFrame to plot a pie chart that visualizes the composition of the member’s portfolio. The y-axis of the pie chart usesamount. Be sure to add a title.Using Python, determine if the current portfolio has enough to create an emergency fund as part of the member’s financial plan. Ideally, an emergency fund should equal to three times the member’s monthly income. To do this, implement the following steps:
Create a variable named
emergency_fund_value, and set it equal to three times the value of the member’smonthly_incomeof $12000. (You set this earlier in Part 1).Create a series of three if statements to determine if the member’s total portfolio is large enough to fund the emergency portfolio:
If the total portfolio value is greater than the emergency fund value, display a message congratulating the member for having enough money in this fund.
Else if the total portfolio value is equal to the emergency fund value, display a message congratulating the member on reaching this important financial goal.
Else the total portfolio is less than the emergency fund value, so display a message showing how many dollars away the member is from reaching the goal. (Subtract the total portfolio value from the emergency fund value.)
xxxxxxxxxx#### Step 1: Create a Python list named `savings_data` that has two elements. The first element contains the total value of the cryptocurrency wallet. The second element contains the total value of the stock and bond portions of the portfolio.Step 1: Create a Python list named savings_data that has two elements. The first element contains the total value of the cryptocurrency wallet. The second element contains the total value of the stock and bond portions of the portfolio.¶
xxxxxxxxxx# Consolidate financial assets data into a Python listsavings_data = [total_crypto_wallet, total_stocks_bonds]# Review the Python list savings_datadisplay(savings_data)[92154.405, 72667.1]
xxxxxxxxxx#### Step 2: Use the `savings_data` list to create a Pandas DataFrame named `savings_df`, and then display this DataFrame. The function to create the DataFrame should take the following three parameters:Step 2: Use the savings_data list to create a Pandas DataFrame named savings_df, and then display this DataFrame. The function to create the DataFrame should take the following three parameters:¶
savings_data: Use the list that you just created.columns: Set this parameter equal to a Python list with a single value calledamount.index: Set this parameter equal to a Python list with the values ofcryptoandstock/bond.
xxxxxxxxxx# Create a Pandas DataFrame called savings_df savings_df = pd.DataFrame(savings_data, columns = ['Amount'], index = ['Crypto Value', 'Stocks/Bonds Value'])# Display the savings_df DataFramesavings_df| Amount | |
|---|---|
| Crypto Value | 92154.405 |
| Stocks/Bonds Value | 72667.100 |
xxxxxxxxxx#### Step 3: Use the `savings_df` DataFrame to plot a pie chart that visualizes the composition of the member’s portfolio. The y-axis of the pie chart uses `amount`. Be sure to add a title.Step 3: Use the savings_df DataFrame to plot a pie chart that visualizes the composition of the member’s portfolio. The y-axis of the pie chart uses amount. Be sure to add a title.¶
xxxxxxxxxx# Plot the total value of the member's portfolio (crypto and stock/bond) in a pie chartsavings_df.plot( kind="pie", title="Total Value of My Portfolio", subplots=True, figsize=(10,5))array([<AxesSubplot:ylabel='Amount'>], dtype=object)
xxxxxxxxxx#### Step 4: Using Python, determine if the current portfolio has enough to create an emergency fund as part of the member’s financial plan. Ideally, an emergency fund should equal to three times the member’s monthly income. To do this, implement the following steps:Step 4: Using Python, determine if the current portfolio has enough to create an emergency fund as part of the member’s financial plan. Ideally, an emergency fund should equal to three times the member’s monthly income. To do this, implement the following steps:¶
Step 1. Create a variable named emergency_fund_value, and set it equal to three times the value of the member’s monthly_income of 12000. (You set this earlier in Part 1).
Step 2. Create a series of three if statements to determine if the member’s total portfolio is large enough to fund the emergency portfolio:
If the total portfolio value is greater than the emergency fund value, display a message congratulating the member for having enough money in this fund.
Else if the total portfolio value is equal to the emergency fund value, display a message congratulating the member on reaching this important financial goal.
Else the total portfolio is less than the emergency fund value, so display a message showing how many dollars away the member is from reaching the goal. (Subtract the total portfolio value from the emergency fund value.)
xxxxxxxxxx##### Step 4-1: Create a variable named `emergency_fund_value`, and set it equal to three times the value of the member’s `monthly_income` of 12000. (You set this earlier in Part 1).Step 4-1: Create a variable named emergency_fund_value, and set it equal to three times the value of the member’s monthly_income of 12000. (You set this earlier in Part 1).¶
xxxxxxxxxx# Create a variable named emergency_fund_valueemergency_fund_value = monthly_income * 3print(f'The emergency fund value is: ${emergency_fund_value:.2f}')The emergency fund value is: $36000.00
xxxxxxxxxx##### Step 4-2: Create a series of three if statements to determine if the member’s total portfolio is large enough to fund the emergency portfolio:Step 4-2: Create a series of three if statements to determine if the member’s total portfolio is large enough to fund the emergency portfolio:¶
If the total portfolio value is greater than the emergency fund value, display a message congratulating the member for having enough money in this fund.
Else if the total portfolio value is equal to the emergency fund value, display a message congratulating the member on reaching this important financial goal.
Else the total portfolio is less than the emergency fund value, so display a message showing how many dollars away the member is from reaching the goal. (Subtract the total portfolio value from the emergency fund value.)
xxxxxxxxxx# Evaluate the possibility of creating an emergency fund with 3 conditions:if total_portfolio > emergency_fund_value: print(f'Congratulations for having enough money in this fund!')elif total_portfolio==emergency_fund_value: print(f'Congratulations on reaching this important financial goal!')else: print(f'You are ${emergency_fund_value-total_portfolio} from reaching the goal.')Congratulations for having enough money in this fund!
xxxxxxxxxx## Part 2: Create a Financial Planner for RetirementPart 2: Create a Financial Planner for Retirement¶
xxxxxxxxxx### Create the Monte Carlo SimulationCreate the Monte Carlo Simulation¶
In this section, you’ll use the MCForecastTools library to create a Monte Carlo simulation for the member’s savings portfolio. To do this, complete the following steps:
Make an API call via the Alpaca SDK to get 3 years of historical closing prices for a traditional 60/40 portfolio split: 60% stocks (SPY) and 40% bonds (AGG).
Run a Monte Carlo simulation of 500 samples and 30 years for the 60/40 portfolio, and then plot the results.The following image shows the overlay line plot resulting from a simulation with these characteristics. However, because a random number generator is used to run each live Monte Carlo simulation, your image will differ slightly from this exact image:

- Plot the probability distribution of the Monte Carlo simulation. Plot the probability distribution of the Monte Carlo simulation. The following image shows the histogram plot resulting from a simulation with these characteristics. However, because a random number generator is used to run each live Monte Carlo simulation, your image will differ slightly from this exact image:

- Generate the summary statistics for the Monte Carlo simulation.
xxxxxxxxxx#### Step 1: Make an API call via the Alpaca SDK to get 3 years of historical closing prices for a traditional 60/40 portfolio split: 60% stocks (SPY) and 40% bonds (AGG).Step 1: Make an API call via the Alpaca SDK to get 3 years of historical closing prices for a traditional 60/40 portfolio split: 60% stocks (SPY) and 40% bonds (AGG).¶
xxxxxxxxxx# Set start and end dates of 3 years back from your current date# Alternatively, you can use an end date of 2020-08-07 and work 3 years back from that date start_date = "2018-10-21" end_date = "2021-10-21"# Set number of rows to 1000 to retrieve the maximum amount of rowslimit_rows=1000xxxxxxxxxx# Use the Alpaca get_barset function to make the API call to get the 3 years worth of pricing data# The tickers and timeframe parameters should have been set in Part 1 of this activity # The start and end dates should be updated with the information set above# Remember to add the df property to the end of the call so the response is returned as a DataFramethree_yr_portfolio = alpaca.get_barset( tickers, timeframe, start = start_date, end = end_date).df# Display both the first and last five rows of the DataFramedisplay(three_yr_portfolio.head())display(three_yr_portfolio.tail())| AGG | SPY | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| open | high | low | close | volume | open | high | low | close | volume | |
| time | ||||||||||
| 2021-06-08 00:00:00-04:00 | 114.84 | 114.87 | 114.7800 | 114.82 | 3951702 | 423.11 | 423.21 | 420.32 | 422.27 | 40131441 |
| 2021-06-09 00:00:00-04:00 | 115.43 | 115.43 | 114.9700 | 115.05 | 3418086 | 423.18 | 423.26 | 421.41 | 421.62 | 38368598 |
| 2021-06-10 00:00:00-04:00 | 115.30 | 115.31 | 114.8605 | 115.30 | 3665934 | 422.96 | 424.63 | 421.55 | 423.58 | 43577329 |
| 2021-06-11 00:00:00-04:00 | 115.31 | 115.32 | 115.1600 | 115.24 | 3476373 | 424.19 | 424.42 | 422.82 | 424.31 | 37018077 |
| 2021-06-14 00:00:00-04:00 | 115.18 | 115.18 | 114.9350 | 114.97 | 2999623 | 424.43 | 425.29 | 423.10 | 425.29 | 33128340 |
| AGG | SPY | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| open | high | low | close | volume | open | high | low | close | volume | |
| time | ||||||||||
| 2021-10-21 00:00:00-04:00 | 114.01 | 114.0400 | 113.8423 | 113.85 | 4741154 | 451.76 | 453.829 | 451.31 | 453.61 | 33945007 |
| 2021-10-22 00:00:00-04:00 | 113.97 | 114.1399 | 113.8950 | 114.07 | 3309637 | 453.13 | 454.670 | 451.05 | 453.13 | 50771701 |
| 2021-10-25 00:00:00-04:00 | 114.06 | 114.2700 | 114.0600 | 114.17 | 7088133 | 454.28 | 455.900 | 452.39 | 455.48 | 37918548 |
| 2021-10-26 00:00:00-04:00 | 114.25 | 114.3800 | 114.1100 | 114.37 | 5284755 | 457.20 | 458.490 | 455.56 | 455.97 | 49865704 |
| 2021-10-27 00:00:00-04:00 | 114.66 | 114.9150 | 114.4250 | 114.77 | 6817074 | 456.45 | 457.160 | 453.86 | 453.92 | 48219255 |
xxxxxxxxxx#### Step 2: Run a Monte Carlo simulation of 500 samples and 30 years for the 60/40 portfolio, and then plot the results.Step 2: Run a Monte Carlo simulation of 500 samples and 30 years for the 60/40 portfolio, and then plot the results.¶
xxxxxxxxxx# Configure the Monte Carlo simulation to forecast 30 years cumulative returns# The weights should be split 40% to AGG and 60% to SPY.# Run 500 samples.thirty_year_simulation = MCSimulation( portfolio_data = three_yr_portfolio, weights = [0.40, 0.60], num_simulation = 500, num_trading_days = 252 * 30,)# Review the simulation input datathirty_year_simulation.portfolio_data.head()# Set number of simulationsnum_sims = 500xxxxxxxxxx# Run the Monte Carlo simulation to forecast 30 years cumulative returnsthirty_year_simulation.calc_cumulative_return()Running Monte Carlo simulation number 0. Running Monte Carlo simulation number 10. Running Monte Carlo simulation number 20. Running Monte Carlo simulation number 30. Running Monte Carlo simulation number 40. Running Monte Carlo simulation number 50. Running Monte Carlo simulation number 60. Running Monte Carlo simulation number 70. Running Monte Carlo simulation number 80. Running Monte Carlo simulation number 90. Running Monte Carlo simulation number 100. Running Monte Carlo simulation number 110. Running Monte Carlo simulation number 120. Running Monte Carlo simulation number 130. Running Monte Carlo simulation number 140. Running Monte Carlo simulation number 150. Running Monte Carlo simulation number 160. Running Monte Carlo simulation number 170. Running Monte Carlo simulation number 180. Running Monte Carlo simulation number 190. Running Monte Carlo simulation number 200. Running Monte Carlo simulation number 210. Running Monte Carlo simulation number 220. Running Monte Carlo simulation number 230. Running Monte Carlo simulation number 240. Running Monte Carlo simulation number 250. Running Monte Carlo simulation number 260. Running Monte Carlo simulation number 270. Running Monte Carlo simulation number 280. Running Monte Carlo simulation number 290. Running Monte Carlo simulation number 300. Running Monte Carlo simulation number 310. Running Monte Carlo simulation number 320. Running Monte Carlo simulation number 330. Running Monte Carlo simulation number 340. Running Monte Carlo simulation number 350. Running Monte Carlo simulation number 360. Running Monte Carlo simulation number 370. Running Monte Carlo simulation number 380. Running Monte Carlo simulation number 390. Running Monte Carlo simulation number 400. Running Monte Carlo simulation number 410. Running Monte Carlo simulation number 420. Running Monte Carlo simulation number 430. Running Monte Carlo simulation number 440. Running Monte Carlo simulation number 450. Running Monte Carlo simulation number 460. Running Monte Carlo simulation number 470. Running Monte Carlo simulation number 480. Running Monte Carlo simulation number 490.
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | ... | 490 | 491 | 492 | 493 | 494 | 495 | 496 | 497 | 498 | 499 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | ... | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 |
| 1 | 1.002406 | 1.001230 | 0.998964 | 1.004901 | 0.998732 | 0.995485 | 1.005809 | 1.005240 | 0.995670 | 1.002249 | ... | 1.002349 | 1.001143 | 1.004777 | 0.997456 | 0.998110 | 1.003788 | 0.995242 | 0.999131 | 0.998996 | 1.002754 |
| 2 | 1.002820 | 1.000942 | 1.001263 | 1.001527 | 1.008880 | 0.997732 | 1.014629 | 1.009174 | 0.990208 | 0.998309 | ... | 0.997837 | 1.006001 | 1.004855 | 0.996405 | 0.991924 | 1.015263 | 0.993499 | 0.992840 | 0.998400 | 0.995199 |
| 3 | 1.009061 | 1.002575 | 1.000881 | 1.000417 | 1.012282 | 0.992918 | 1.017654 | 1.002783 | 0.994560 | 0.998209 | ... | 1.003763 | 1.007444 | 1.006510 | 0.997607 | 0.991335 | 1.007901 | 0.987671 | 0.996186 | 1.001325 | 0.985407 |
| 4 | 1.016086 | 0.998357 | 1.000422 | 1.004517 | 1.013847 | 0.993105 | 1.013339 | 0.998971 | 0.993129 | 0.999413 | ... | 1.000209 | 1.005411 | 1.008640 | 1.002152 | 0.996362 | 1.000718 | 0.986652 | 0.987922 | 1.014331 | 0.991284 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 7556 | 28.030911 | 37.405939 | 24.668193 | 47.209859 | 22.520441 | 23.652848 | 31.651462 | 30.615852 | 39.231077 | 23.520117 | ... | 68.341627 | 27.349744 | 31.023659 | 35.284010 | 50.207835 | 27.869737 | 23.344379 | 16.620935 | 34.525911 | 62.741476 |
| 7557 | 28.100679 | 37.350037 | 24.743653 | 47.183273 | 22.516468 | 23.647467 | 31.650875 | 30.826689 | 39.390006 | 23.566772 | ... | 68.091843 | 27.457265 | 30.901467 | 35.299930 | 50.077861 | 27.786322 | 23.383160 | 16.627983 | 34.585619 | 62.590230 |
| 7558 | 27.984769 | 37.398970 | 24.712191 | 47.169630 | 22.426692 | 23.778172 | 31.644927 | 30.773266 | 39.758511 | 23.676854 | ... | 67.902286 | 27.521867 | 30.885856 | 34.943243 | 50.344072 | 27.869713 | 23.441258 | 16.519794 | 34.336454 | 62.439266 |
| 7559 | 27.884567 | 37.587090 | 24.718771 | 47.193397 | 22.449825 | 23.950313 | 31.626778 | 30.950165 | 39.652674 | 23.614836 | ... | 68.242568 | 27.588266 | 30.868128 | 35.243832 | 50.149038 | 27.944404 | 23.613556 | 16.542526 | 34.318662 | 62.309319 |
| 7560 | 27.879213 | 37.317320 | 24.883653 | 47.342378 | 22.320469 | 23.949491 | 31.662563 | 31.219180 | 39.750457 | 23.624769 | ... | 68.090630 | 27.645684 | 30.789391 | 35.544020 | 50.010503 | 28.034279 | 23.570843 | 16.679932 | 34.583051 | 62.885517 |
7561 rows × 500 columns
xxxxxxxxxx### Visualize the 30-year Monte Carlo simulation by creating an# overlay line plotthirty_year_simulation.plot_simulation()<AxesSubplot:title={'center':'500 Simulations of Cumulative Portfolio Return Trajectories Over the Next 7560 Trading Days.'}>xxxxxxxxxx#### Step 3: Plot the probability distribution of the Monte Carlo simulation.Step 3: Plot the probability distribution of the Monte Carlo simulation.¶
xxxxxxxxxx# Visualize the probability distribution of the 30-year Monte Carlo simulation # by plotting a histogramthirty_year_simulation.plot_distribution()<AxesSubplot:title={'center':'Distribution of Final Cumuluative Returns Across All 500 Simulations'}, ylabel='Frequency'>xxxxxxxxxx#### Step 4: Generate the summary statistics for the Monte Carlo simulation.Step 4: Generate the summary statistics for the Monte Carlo simulation.¶
xxxxxxxxxx# Generate summary statistics from the 30-year Monte Carlo simulation results# Save the results as a variablethirty_tbl=thirty_year_simulation.summarize_cumulative_return()# Review the 30-year Monte Carlo summary statisticsthirty_tblcount 500.000000 mean 31.158117 std 11.825937 min 9.948483 25% 23.042305 50% 29.168570 75% 36.910780 max 76.927008 95% CI Lower 14.285925 95% CI Upper 62.288960 Name: 7560, dtype: float64
xxxxxxxxxx### Analyze the Retirement Portfolio ForecastsAnalyze the Retirement Portfolio Forecasts¶
Using the current value of only the stock and bond portion of the member's portfolio and the summary statistics that you generated from the Monte Carlo simulation, answer the following question in your Jupyter notebook:
- What are the lower and upper bounds for the expected value of the portfolio with a 95% confidence interval?
xxxxxxxxxx# Print the current balance of the stock and bond portion of the members portfolioprint(f'The current balance of the stock and bond portion of the portfolio is: ${total_stocks_bonds:.2f}')The current balance of the stock and bond portion of the portfolio is: $72667.10
xxxxxxxxxx# Use the lower and upper `95%` confidence intervals to calculate the range of the possible outcomes for the current stock/bond portfolioci_lower_thirty_cumulative_return = round(thirty_tbl[8] * total_stocks_bonds,2)ci_upper_thirty_cumulative_return = round(thirty_tbl[9] * total_stocks_bonds,2)# Print the result of your calculationsprint(f"There is a 95% chance that your current stock/bond portfolio" f" over the next year will end within in the range of" f" ${ci_lower_thirty_cumulative_return} and ${ci_upper_thirty_cumulative_return}.")There is a 95% chance that your current stock/bond portfolio over the next year will end within in the range of $1038116.74 and $4526358.08.
xxxxxxxxxx### Forecast Cumulative Returns in 10 YearsForecast Cumulative Returns in 10 Years¶
The CTO of the credit union is impressed with your work on these planning tools but wonders if 30 years is a long time to wait until retirement. So, your next task is to adjust the retirement portfolio and run a new Monte Carlo simulation to find out if the changes will allow members to retire earlier.
For this new Monte Carlo simulation, do the following:
Forecast the cumulative returns for 10 years from now. Because of the shortened investment horizon (30 years to 10 years), the portfolio needs to invest more heavily in the riskier asset—that is, stock—to help accumulate wealth for retirement.
Adjust the weights of the retirement portfolio so that the composition for the Monte Carlo simulation consists of 20% bonds and 80% stocks.
Run the simulation over 500 samples, and use the same data that the API call to Alpaca generated.
Based on the new Monte Carlo simulation, answer the following questions in your Jupyter notebook:
Using the current value of only the stock and bond portion of the member's portfolio and the summary statistics that you generated from the new Monte Carlo simulation, what are the lower and upper bounds for the expected value of the portfolio (with the new weights) with a 95% confidence interval?
Will weighting the portfolio more heavily toward stocks allow the credit union members to retire after only 10 years?
xxxxxxxxxx# Configure a Monte Carlo simulation to forecast 10 years cumulative returns# The weights should be split 20% to AGG and 80% to SPY.# Run 500 samples.ten_year_simulation = MCSimulation( portfolio_data=three_yr_portfolio, weights=[0.20, 0.80], num_simulation=500, num_trading_days=252*10,)# Review the simulation input dataten_year_simulation.portfolio_data.head()| AGG | SPY | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| open | high | low | close | volume | daily_return | open | high | low | close | volume | daily_return | |
| time | ||||||||||||
| 2021-06-08 00:00:00-04:00 | 114.84 | 114.87 | 114.7800 | 114.82 | 3951702 | NaN | 423.11 | 423.21 | 420.32 | 422.27 | 40131441 | NaN |
| 2021-06-09 00:00:00-04:00 | 115.43 | 115.43 | 114.9700 | 115.05 | 3418086 | 0.002003 | 423.18 | 423.26 | 421.41 | 421.62 | 38368598 | -0.001539 |
| 2021-06-10 00:00:00-04:00 | 115.30 | 115.31 | 114.8605 | 115.30 | 3665934 | 0.002173 | 422.96 | 424.63 | 421.55 | 423.58 | 43577329 | 0.004649 |
| 2021-06-11 00:00:00-04:00 | 115.31 | 115.32 | 115.1600 | 115.24 | 3476373 | -0.000520 | 424.19 | 424.42 | 422.82 | 424.31 | 37018077 | 0.001723 |
| 2021-06-14 00:00:00-04:00 | 115.18 | 115.18 | 114.9350 | 114.97 | 2999623 | -0.002343 | 424.43 | 425.29 | 423.10 | 425.29 | 33128340 | 0.002310 |
xxxxxxxxxx# Run the Monte Carlo simulation to forecast 10 years cumulative returnsten_year_simulation.calc_cumulative_return()Running Monte Carlo simulation number 0. Running Monte Carlo simulation number 10. Running Monte Carlo simulation number 20. Running Monte Carlo simulation number 30. Running Monte Carlo simulation number 40. Running Monte Carlo simulation number 50. Running Monte Carlo simulation number 60. Running Monte Carlo simulation number 70. Running Monte Carlo simulation number 80. Running Monte Carlo simulation number 90. Running Monte Carlo simulation number 100. Running Monte Carlo simulation number 110. Running Monte Carlo simulation number 120. Running Monte Carlo simulation number 130. Running Monte Carlo simulation number 140. Running Monte Carlo simulation number 150. Running Monte Carlo simulation number 160. Running Monte Carlo simulation number 170. Running Monte Carlo simulation number 180. Running Monte Carlo simulation number 190. Running Monte Carlo simulation number 200. Running Monte Carlo simulation number 210. Running Monte Carlo simulation number 220. Running Monte Carlo simulation number 230. Running Monte Carlo simulation number 240. Running Monte Carlo simulation number 250. Running Monte Carlo simulation number 260. Running Monte Carlo simulation number 270. Running Monte Carlo simulation number 280. Running Monte Carlo simulation number 290. Running Monte Carlo simulation number 300. Running Monte Carlo simulation number 310. Running Monte Carlo simulation number 320. Running Monte Carlo simulation number 330. Running Monte Carlo simulation number 340. Running Monte Carlo simulation number 350. Running Monte Carlo simulation number 360. Running Monte Carlo simulation number 370. Running Monte Carlo simulation number 380. Running Monte Carlo simulation number 390. Running Monte Carlo simulation number 400. Running Monte Carlo simulation number 410. Running Monte Carlo simulation number 420. Running Monte Carlo simulation number 430. Running Monte Carlo simulation number 440. Running Monte Carlo simulation number 450. Running Monte Carlo simulation number 460. Running Monte Carlo simulation number 470. Running Monte Carlo simulation number 480. Running Monte Carlo simulation number 490.
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | ... | 490 | 491 | 492 | 493 | 494 | 495 | 496 | 497 | 498 | 499 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | ... | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 | 1.000000 |
| 1 | 0.996686 | 1.003969 | 1.004916 | 0.994064 | 1.000928 | 0.995919 | 0.995431 | 1.002035 | 1.000696 | 1.002050 | ... | 0.995774 | 0.998322 | 0.993781 | 1.010995 | 1.005748 | 1.002060 | 0.995622 | 1.003322 | 1.004654 | 0.987432 |
| 2 | 0.996002 | 0.996378 | 1.000512 | 0.998666 | 0.999387 | 0.985237 | 0.992027 | 1.001916 | 1.002718 | 1.006047 | ... | 1.003029 | 0.999611 | 0.994926 | 1.012113 | 1.008234 | 1.004870 | 0.988352 | 0.998783 | 1.019706 | 0.985252 |
| 3 | 1.001908 | 0.999628 | 0.993864 | 0.998196 | 0.989443 | 0.986249 | 0.991462 | 1.000197 | 1.001540 | 1.012195 | ... | 1.008237 | 1.001192 | 0.998106 | 1.015770 | 1.010172 | 1.005718 | 0.986360 | 0.993262 | 1.021829 | 0.987701 |
| 4 | 1.008757 | 1.006673 | 0.996856 | 0.998135 | 0.982043 | 0.994561 | 0.987042 | 1.012912 | 0.997258 | 1.007512 | ... | 1.005693 | 0.993249 | 0.994319 | 1.014219 | 1.007934 | 1.009813 | 0.983497 | 0.988252 | 1.022506 | 0.991632 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 2516 | 5.528957 | 5.106858 | 4.159647 | 3.679087 | 4.240004 | 4.542804 | 4.145397 | 5.472170 | 6.214816 | 3.148933 | ... | 2.487638 | 5.326155 | 3.974700 | 3.096506 | 7.626031 | 3.945279 | 5.200642 | 4.883835 | 4.527688 | 2.653837 |
| 2517 | 5.533872 | 5.100893 | 4.172068 | 3.658683 | 4.255179 | 4.558117 | 4.125328 | 5.497043 | 6.178001 | 3.177241 | ... | 2.484584 | 5.317688 | 3.938684 | 3.108611 | 7.634717 | 3.965337 | 5.194737 | 4.915034 | 4.527685 | 2.650982 |
| 2518 | 5.520605 | 5.152993 | 4.152630 | 3.649069 | 4.280630 | 4.563013 | 4.165554 | 5.513886 | 6.230247 | 3.182171 | ... | 2.486697 | 5.325275 | 3.939582 | 3.098599 | 7.619213 | 3.989910 | 5.169170 | 4.943450 | 4.519936 | 2.647963 |
| 2519 | 5.496475 | 5.125652 | 4.167166 | 3.655188 | 4.280573 | 4.587955 | 4.168351 | 5.531902 | 6.253192 | 3.155961 | ... | 2.469957 | 5.327570 | 3.932455 | 3.102091 | 7.559300 | 3.989145 | 5.117184 | 4.959662 | 4.539151 | 2.647139 |
| 2520 | 5.471286 | 5.166573 | 4.169099 | 3.656978 | 4.263659 | 4.600532 | 4.141753 | 5.578293 | 6.261477 | 3.186749 | ... | 2.469989 | 5.374526 | 3.921654 | 3.100162 | 7.612399 | 3.983899 | 5.119149 | 4.968517 | 4.485957 | 2.631524 |
2521 rows × 500 columns
xxxxxxxxxx# Visualize the 10-year Monte Carlo simulation by creating an# overlay line plotten_year_simulation.plot_simulation()<AxesSubplot:title={'center':'500 Simulations of Cumulative Portfolio Return Trajectories Over the Next 2520 Trading Days.'}>xxxxxxxxxx# Visualize the probability distribution of the 10-year Monte Carlo simulation # by plotting a histogramten_year_simulation.plot_distribution()<AxesSubplot:title={'center':'Distribution of Final Cumuluative Returns Across All 500 Simulations'}, ylabel='Frequency'>xxxxxxxxxx# Generate summary statistics from the 10-year Monte Carlo simulation results# Save the results as a variableten_tbl=ten_year_simulation.summarize_cumulative_return()# Review the 10-year Monte Carlo summary statisticsten_tblcount 500.000000 mean 4.507405 std 1.283335 min 2.048672 25% 3.552285 50% 4.338490 75% 5.297036 max 9.637028 95% CI Lower 2.458394 95% CI Upper 7.593682 Name: 2520, dtype: float64
xxxxxxxxxx### Answer the following questions:Answer the following questions:¶
xxxxxxxxxx#### Question: Using the current value of only the stock and bond portion of the member's portfolio and the summary statistics that you generated from the new Monte Carlo simulation, what are the lower and upper bounds for the expected value of the portfolio (with the new weights) with a 95% confidence interval?Question: Using the current value of only the stock and bond portion of the member's portfolio and the summary statistics that you generated from the new Monte Carlo simulation, what are the lower and upper bounds for the expected value of the portfolio (with the new weights) with a 95% confidence interval?¶
xxxxxxxxxx# Print the current balance of the stock and bond portion of the members portfolioprint(f'The current balance of the stock and bond portion of the portfolio is: ${total_stocks_bonds:.2f}')The current balance of the stock and bond portion of the portfolio is: $72667.10
xxxxxxxxxx# Use the lower and upper `95%` confidence intervals to calculate the range of the possible outcomes for the current stock/bond portfolioci_lower_ten_cumulative_return = round(ten_tbl[8] * total_stocks_bonds,2)ci_upper_ten_cumulative_return = round(ten_tbl[9] * total_stocks_bonds,2)# Print the result of your calculationsprint(f"There is a 95% chance that your current stock/bond portfolio" f" over the next ten years will end within in the range of" f" ${ci_lower_ten_cumulative_return} and ${ci_upper_ten_cumulative_return}.")There is a 95% chance that your current stock/bond portfolio over the next ten years will end within in the range of $178644.35 and $551810.81.
xxxxxxxxxx#### Question: Will weighting the portfolio more heavily to stocks allow the credit union members to retire after only 10 years?Question: Will weighting the portfolio more heavily to stocks allow the credit union members to retire after only 10 years?¶
xxxxxxxxxx**Answer** It depends on the credit union member's age, life expectancy and lifestyle. Based on the summary statistics for the ten year analysis, the current portfolio has a 95% probability to grow 2.89% to 8.63% versus 95% probability to grow 17.46% to 71.11% for the 30 year analysis.- etf_analyzer.ipynb